0

is it possible to change multiple field values when using CASE statement?

I have a Text field with several values ( 5 differnt ones) and each of this value has its own Boolean field in tragt Data Extension.

Is there a way to combine those CASE statements? I dont get it working.

I used this as a basis:

SELECT
 Key,

, case 
    when Source in ('Web') then 1 
    else 0 
  end As Source_Web
, case 
    when Source in ('facebook') then 1 
    else 0 
 end As Source_facebook
FROM [Old_DE]

This would continue until all Values are fetched but it is not working. Anyone able to help out?

UPDATE

Let me elaborate more on it:

OLD DE:

Key, Country, Source
123456,SW,Phone
123456,US,Web
123456,IT,Phone
123456,Clara,facbook


New Data Extension has structure below (source fields are boolean fields and should updated to true(

Outcome of the query should look like:

Key,Source_Phone,Source_Web,Source_facebook
123456,1,1,1

hope that makes my problem more clear

  • Can you elaborate a bit. Do you mean there are 5 different value outputs you want inside of a conditon for a single field. E.g. case when x = 1 then a when y=1 then b.... end as value or will each output 5 different values depending on the 5 different fields (like in your example with phone and personal)? – Gortonington Mar 11 at 20:06
  • Hi @Gortonington Let me elaborate on it: OLD DE: 'Key, Country, Source 123456,SW,Phone 123456,US,Web 123456,IT,Phone 123456,Clara,facbook New Data Extension (source fields are boolean fields and should updated to true Outcome of the query should look like: Key,Source_Phone,Source_Web,Source_facebook 123456,1,1,1 hope that makes my problem more clear – Werner Holm Mar 11 at 21:31
2

Let break this down in to steps:


First, start by giving each record a value of 1 in the column where where applicable

SELECT
    Key,
    IIF(Source = 'Phone',1,0) as Source_Phone,
    IIF(Source = 'Web',1,0) as Source_Web,
    IIF(Source = 'facebook',1,0) as Source_facebook

FROM [Old_DE]

Based on your example above, you'd get a result set as such

Key   |Source_Phone|Source_Web|Source_facebook
------+------------+----------+---------------
123456|     1      |    0     |      0
123456|     0      |    1     |      0
123456|     1      |    0     |      0
123456|     0      |    0     |      1

Secondly, you want to combine the the records to get an aggregate count of applicable records.

SELECT
    Key,
    SUM(IIF(Source = 'Phone',1,0)) as Source_Phone,
    SUM(IIF(Source = 'Web',1,0)) as Source_Web,
    SUM(IIF(Source = 'facebook',1,0)) as Source_facebook

FROM [Old_DE]

GROUP BY Key

which will result in

Key   |Source_Phone|Source_Web|Source_facebook
------+------------+----------+---------------
123456|     2      |    1     |      1

Lastly, since you're looking to insert as boolean, you need to address numbers greater than 0. That's just another IF to transform any number > 0 in to 1.

SELECT
    Key,
    IIF(SUM(IIF(Source = 'Phone',1,0)) > 0, 1, 0) as Source_Phone,
    IIF(SUM(IIF(Source = 'Web',1,0)) > 0, 1, 0) as Source_Web,
    IIF(SUM(IIF(Source = 'facebook',1,0)) > 0, 1, 0) as Source_facebook

FROM [Old_DE]

GROUP BY Key

which will result in your final desired set which will insert into your boolean fields properly.

Key   |Source_Phone|Source_Web|Source_facebook
------+------------+----------+---------------
123456|     1      |    1     |      1
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.