SFMC SQL Case
Empower your Marketing Cloud queries with conditional SQL CASE logic.
Conditional values with CASE
CASE statement evaluates a list of conditions and returns one of the multiple possible result expressions. For Marketing Cloud purposes, it is especially useful when you want to select a column and modify the values within it. Perfect for translating numeric codes to descriptive ones for personalisation purposes. Excellent for standardising data (change those 1, true and on values of opt-in to a single format).
SELECT
ContactKey
, EmailAddress
, [External Contact Id] AS ExternalContactId
, Gender__c AS Gender
, 'Lead' AS RecordType
, GETUTCDATE() AS ImportDate
, CASE MemberType__c
WHEN 1 THEN 'Bronze'
WHEN 2 THEN 'Silver'
WHEN 3 THEN 'Gold'
WHEN 4 THEN 'Platinum'
ELSE 'Unregistered'
END AS MemberType
You can have as many WHEN/THEN conditions as you want. Also, the ELSE statement is optional but a good idea to catch unexpected values.
Feel free also to leverage aliases to manipulate the column name at the same time:
SELECT
ContactKey
, EmailAddress
, [External Contact Id] AS ExternalContactId
, Gender__c AS Gender
, 'Lead' AS RecordType
, GETUTCDATE() AS ImportDate
, CASE MemberType__c
WHEN 1 THEN 'Bronze'
WHEN 2 THEN 'Silver'
WHEN 3 THEN 'Gold'
WHEN 4 THEN 'Platinum'
ELSE 'Unregistered'
END AS MemberType
Search version of CASE
The above examples are already helpful in segmentation, but there is also one more way to write and use a CASE statement that gives even more power. The search CASE.
The difference is that you are not passing the column name on which you will be building conditions. Instead, you create a boolean expression from scratch for each scenario.
SELECT
ContactKey
, EmailAddress
, [External Contact Id] AS ExternalContactId
, Gender__c AS Gender
, 'Lead' AS RecordType
, GETUTCDATE() AS ImportDate
, CASE
WHEN MemberType__c = 1 THEN 'Bronze'
WHEN MemberType__c = 2 THEN 'Silver'
WHEN MemberType__c = 3 THEN 'Gold'
WHEN MemberType__c = 4 THEN 'Platinum'
ELSE 'Unregistered'
END AS MemberType
The example above doesn't show why it is worth using the search CASE. It is a bit longer than the standard approach! But there is a reason to use this form of CASE statement that is visible once you want to create more elaborate conditions that are possible only with this version:
SELECT
ContactKey
, CASE
WHEN IsAppInstalled = 1 THEN 'Push'
WHEN EmailOptIn = 1 AND EmailAddress IS NOT NULL THEN 'Email'
WHEN PhoneOptIn = 1 AND Mobile IS NOT NULL THEN 'Phone'
END AS PreferredChannel
CASE statement returns THEN value of the first condition that evaluated to TRUE. In the above example, if the IsAppInstalled = 1 is true, other conditions are not considered. Even if for selected contact all of them will be true, it will still have the value Push in PreferredChannel. It is perfect, as it allows us to prioritise cheaper channels.
Always consider the order of the conditions in your CASE statement. It both might lead to errors if you don't expect the behaviour mentioned above and make your life much easier if you know it works that way.
As you can see, thanks to search CASE statements, we can check conditions on multiple different columns and connect them with AND/OR statements for better control. You can use this version of the CASE statement for all your needs (even those covered by the basic one) to not worry about two ways of formatting.
You can use CASE statements inside a CASE statement to have an even more dynamic query.
SELECT
ContactKey
, CASE
WHEN MemberType = 'Platinum' THEN 'Key Account Manager'
WHEN MemberType = 'Gold' OR MemberType = 'Silver' THEN
CASE
WHEN IsAppInstalled = 1 THEN 'Push'
WHEN EmailOptIn = 1 AND EmailAddress IS NOT NULL THEN 'Email'
WHEN PhoneOptIn = 1 AND Mobile IS NOT NULL THEN 'Phone'
END
WHEN MemberType = 'Bronze' THEN
CASE
WHEN IsAppInstalled = 1 THEN 'Push'
WHEN EmailOptIn = 1 AND EmailAddress IS NOT NULL THEN 'Email'
END
END AS PreferredChannel
LIKE in CASE
LIKE is not only valuable for the WHERE Statement. It is also an incredible tool empowering the CASE statement. It allows you to build flexible conditions on free text values:
SELECT
ContactKey
, CASE
WHEN EmailAddress LIKE '%@mateuszdabrowski.%' THEN 'Internal'
ELSE 'External'
END AS ContactType
In the SELECT statement, the LIKE operator is handy for fixing data coming from the free text fields. To learn more about the possibilities, check out the LIKE guide.
IIF Shorthand
The CASE statement is a fantastic tool but can be overkill for simple conditions. The IIF comes to the rescue.
It takes three arguments:
- Condition
- The value returned if the condition evaluates to true
- The value returned if the condition evaluates to false
With it, instead of multiline CASE:
SELECT
_Subscriber.SubscriberKey
, CASE
WHEN _Subscriber.Status = 'active' THEN 'TRUE'
ELSE 'FALSE'
END AS Sendable
You can have the same outcome in a single, more readable IIF line:
SELECT
_Subscriber,SubscriberKey,
, IIF(_Subscriber.Status = 'active', 'TRUE', 'FALSE') AS Sendable
Whenever you have a single condition - go with IIF.
You can take both CASE and IIF to the next level with dynamic values. Don't limit yourself to a string, integer or boolean - pass a column name.
Use case? Think of a Master Data Extension that stores a massive amount of contact information, including preferred channel (Email or Mobile). With dynamic values and IIF in just a few lines, we can create an optimised Data Extension with crucial information:
SELECT
SubscriberKey
, PreferredChannel
, IIF(PreferredChannel = 'Email', EmailAddress, MobileNumber) AS PointOfContact
Now, even with just three columns in your Entry Point, you can create a Journey that adapts the channel to customer preferences.
CASE in WHERE
The CASE statement is not only useful in a SELECT part of the query. You can use it also within the WHERE to apply conditional filtering.