How to use if statement in Marketing Cloud SQL activity?

Question

In the syncronized data extensions Account__c i have the following fields:

• KPIUltimoSoggiornoTipo__c that is mapped with a list of products name (it is a text field)
• KPIClusterFrequenza__c indecates the frequency (first timer or repeater)

IF KPIUltimoSoggiornoTipo__c = 'stagionali' i want to associate both KPIClusterFrequenza__c ='Repeater' and KPIClusterFrequenza__c = 'first_timer'

If KPIUltimoSoggiornoTipo__c != 'stagionali' I want to associate only KPIClusterFrequenza__c ='Repeater'

I set up the following instructions, but they are not working:

IF (B.KPIUltimoSoggiornoTipo__c = 'STAGIONALI')
then B.KPIClusterFrequenza__c = 'REPEATER' OR B.KPIClusterFrequenza__c = 'FIRST_TIMER'
ELSE B.KPIClusterFrequenza__c = 'REPEATER'

What do I miss? Thank you in advance, for your kind help

Answer 1

You would want to use CASE Expression.

Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Assuming you are querying from a child BU, then it would be something like:

SELECT
KPIUltimoSoggiornoTipo__c,
CASE
    WHEN KPIUltimoSoggiornoTipo__c = 'STAGIONALI' THEN 'REPEATER'
    ELSE 'FIRST_TIMER'
END AS KPIClusterFrequenza__c 
FROM
ENT.[Your syncronized Account DE name]