1

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

2
  • 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)? Mar 11, 2020 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 Mar 11, 2020 at 21:31

1 Answer 1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .