3

I've had a few different kinds of query return the following error message, quite often when I'm using 'case when':

"An error occurred while checking the query syntax. Errors: Old style JOIN (ANSI JOINs) syntax is not allowed. Please use standard syntax."

The SQL I'm trying at the moment is:

select 'Rolling' as [Group]
    ,   case 
        when account_number in (select account_number from [email marketable]) then 'Y'
        else 'N'
        end as Emailable
    ,   case 
        when account_number in (Select account_number from [sms marketable]) then 'Y'
        else 'N'
        end as SMSable
from [12mth_shoppers] 

If I comment out either of the 'case' elements, the syntax will validate fine but when I include both of them I get the above error message.

5

I'd do something like this with left joins:

select 
'Rolling' as [Group]
, case 
    when em.account_number is not null then 'Y'
    else 'N'
  end Emailable
, case 
    when sm.account_number is not null then 'Y'
    else 'N'
  end SMSable
from [12mth_shoppers] s
left join (
    select distinct em0.account_number 
    from [email marketable] em0
) em on em.account_number = s.account_number
left join (
    select distinct sm0.account_number 
    from [sms marketable] sm0
) sm on sm.account_number = s.account_number

Or if your secondary data extensions are unique by account_number:

select 
'Rolling' as [Group]
, case 
    when em.account_number is not null then 'Y'
    else 'N'
  end Emailable
, case 
    when sm.account_number is not null then 'Y'
    else 'N'
  end SMSable
from [12mth_shoppers] s
left join [email marketable] em on em.account_number = s.account_number
left join [sms marketable] sm on em.account_number = s.account_number
| improve this answer | |
  • Thank you for this! Both queries validated (which is a step up from mine) but the second one wouldn't run due to "Query activity was terminated. Reducing query complexity helps prevent this error in many cases." The first one ran and seems to have done what I was looking for, thank you! Can I ask why you think mine was producing the error? I've done CASE WHEN before using the "where account_number is in (select account_number from [Data_Extension])" and it's worked fine- why do you think this one would fail? – nc_analyst Jan 14 at 8:32
  • SFMC wraps queries in other queries behind the scenes, so they've restricted certain valid patterns to accommodate. – Adam Spriggs Jan 14 at 12:25

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.