Marketing Cloud SQL Case Function

Question

I have an already existing SQL function running in Marketing Cloud and have been attempting to add on a CASE function to determine a column of "State". I have tried a couple different versions of this and not recieved any syntax errors, however it is not functioning as I intended.

The targeted Data Extension has a field of State so the case results would transfer from the current column Home_Area__c to State. Additionally the results from the evaluation would drop into that column.

Any ideas on what I am missing?

Answer 1

In your query, you're considering the field name 'State' (with single quotes).

If in your result Data Extension you have the fields Home_Area__c and State (without single quotes), but your query returns the fields Home_Area__c and 'State', only the field Home_Area__c will be populated (because 'State' doesn't exist in your result Data Extension)

You need to remove the single quotes in END as 'State':

Case
  When Home_Area__c = 'Phoenix' then 'AZ'
  When Home_Area__c = 'Tucson' then 'AZ'
  When Home_Area__c = 'Dallas/FW' then 'TX'
  ELSE 'No Config'
End as State

Answer 2

In your snippet, you have target data extension column name in single quote due to which it is not giving proper result. It will consider 'State' as value. Please try below snippet.

Case When Home_Area__c = 'Phoenix' then 'AZ' . . . End as Status