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

Question

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.

Answer 1

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