0

I am creating this SQL query in Automation Studio. This query gives me an error:

An error occurred while checking the query syntax. Errors: Error while parsing query. Please check the syntax

When adding the last AND statement the error shows. Here's my querycode:

SELECT x.Email, x.Country, x.First_Name, x.Date_of_Birth, x.Age,
CASE
        WHEN Country IN ('US', 'CA') THEN 'NA'
        WHEN Country IN ('DE', 'FR', 'GB', 'DK', 'FI', 'SE', 'AT', 'PL', 'PT', 'NO', 'NL', 'IE', 'BE') THEN 'EU'
        ELSE 'ASIA'
    END as Region
, datediff(day, getdate(), x.nextBirthDay) AS 'Days_Before_Birthday'
FROM 
(
SELECT
a.Email
, a.Date_Of_Birth
, a.First_Name
, datediff(year,a.Date_Of_Birth, getDate()) as 'Age'
, CASE
        WHEN (dateadd(year,datediff(year,a.Date_Of_Birth,getDate()), a.Date_Of_Birth) > getDate()) THEN dateadd(Year,datediff(year,a.Date_Of_Birth,getDate()), a.Date_Of_Birth)
        ELSE dateadd(year, datediff(year,a.Date_Of_Birth,getDate())+1 , a.Date_Of_Birth)
    END as nextBirthDay
    from [Master DB] a
    where a.Date_Of_Birth is not null
) x
WHERE
datediff(day, getdate(), x.nextBirthDay) <= 1 
AND x.Email NOT IN(Select Email FROM [Welcome_Journey_Exclusion DE])
AND x.Country IN ('GB','US','CA','DK', 'DE','FR','SG','HK', 'FI', 'SE', 'AU', 'IE', 'BE', 'NL', 'NO', 'PT', 'PL', 'AT')

Thank you in advance.

1

Reverting the last two conditions in the where clause works for me.

enter image description here

1
  • Yup that's also what I did! – CAR Sep 29 '20 at 17:13
0

You're not selecting Country in your subquery. Assuming it's present in the [Master DB] data extension:

SELECT 
  x.Email
, x.Country
, x.First_Name
, x.Date_of_Birth
, x.Age
, CASE
        WHEN x.Country IN ('US', 'CA') THEN 'NA'
        WHEN x.Country IN ('DE', 'FR', 'GB', 'DK', 'FI', 'SE', 'AT', 'PL', 'PT', 'NO', 'NL', 'IE', 'BE') THEN 'EU'
        ELSE 'ASIA'
   END as Region
, datediff(day, getdate(), x.nextBirthDay) AS 'Days_Before_Birthday'
FROM 
(
  SELECT
    a.Email
  , a.Country
  , a.Date_Of_Birth
  , a.First_Name
  , datediff(year,a.Date_Of_Birth, getDate()) as 'Age'
  , CASE
     WHEN (dateadd(year,datediff(year,a.Date_Of_Birth,getDate()), a.Date_Of_Birth) > getDate()) THEN dateadd(Year,datediff(year,a.Date_Of_Birth,getDate()), a.Date_Of_Birth)
     ELSE dateadd(year, datediff(year,a.Date_Of_Birth,getDate())+1 , a.Date_Of_Birth)
    END as nextBirthDay
  from [Master DB] a
  where a.Date_Of_Birth is not null
) x
WHERE
datediff(day, getdate(), x.nextBirthDay) <= 1 
AND x.Email NOT IN(Select Email FROM [Welcome_Journey_Exclusion DE])
AND x.Country IN ('GB','US','CA','DK', 'DE','FR','SG','HK', 'FI', 'SE', 'AU', 'IE', 'BE', 'NL', 'NO', 'PT', 'PL', 'AT')
3
  • Hi @Adam it is present in [Master DB] and adding Country to the subquery is not the fixed :( still getting an error – CAR Sep 28 '20 at 3:32
  • Just checked the syntax of my version of your query in my local SQL Server installation and it's valid. What error are you getting now? – Adam Spriggs Sep 28 '20 at 13:39
  • I got the error the same, but now fixed I just switch the positions of NOT IN and IN statements in the where clause. – CAR Sep 29 '20 at 7:49

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.