-2

I'm trying to create a data extension that extracts subscriber key and clicks based on specific criteria. Below is the script:

select c.[JobID]
     , c.[SubscriberKey]
     , c.[URL]
     , j.[EmailName]
     , c.[LinkName]
from [_Click] c
INNER JOIN [_Job] j ON j.[JobID] = c.[JobID]
WHERE j.[EmailName] like '%Onboarding email%'
OR j.[EmailName] IN ('Onboarding3', 'Onboarding4')
AND c.[URL] like '%review%'
OR c.[URL] like '%action%'
OR c.[LinkName] like '%B&I%'

This gets me the data that I need which is great. But, I want to add an additional column to my data extension named: Category

And I want it to give a category based on the above filters (two different columns), i.e., if URL has 'review', then the category is Review. If URL has 'action', then the category is Action. And if the Link name has 'B&I', then the category is 'B&I'.

Can someone give me tips on how to go about this? Thank you

1
  • 1
    I suspect you are missing some parentheses in your WHERE clause, in order for your logical operators to function as expected. May 17, 2023 at 4:45

1 Answer 1

1

You can use SQL Case Expression to return a value based on a condition.

Try this:

SELECT 
  c.[JobID] ,
  c.[SubscriberKey] ,
  c.[URL] ,
  j.[EmailName] ,
  c.[LinkName],
  CASE
    WHEN c.[URL] like '%review%' THEN 'review'
    WHEN c.[URL] like '%action%' THEN 'action'
    WHEN c.[LinkName] like '%B&I%' THEN 'B&I'
    ELSE 'something else'
  END AS Category
FROM [_Click] c
INNER JOIN [_Job] j ON j.[JobID] = c.[JobID]
WHERE j.[EmailName] like '%Onboarding email%'
  OR j.[EmailName] IN ('Onboarding3',
                       'Onboarding4')
  AND c.[URL] like '%review%'
  OR c.[URL] like '%action%'
  OR c.[LinkName] like '%B&I%'

Note: I haven't tested on it.

1
  • Thank you so much, this worked! May 17, 2023 at 5:53

You must log in to answer this question.

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