1

Hi everybody iam facing some issues. I want to create a sql activity which gives me the most recent record based on my date field called (created date) from DE: integration 2play and saves those records (green) only to new DE.

* Due to comments i changed code accordingly but still getting error: Incorrect syntax near the keyword 'as'.*

Any suggestions?

Please find my code below.

Hope any body can help me out.

enter image description here

 SELECT ID1, ID2, Name, [Phone number], [last login], MAX[created date] as [created date], [email address]    
    FROM [integration 2play]           
    GROUP BY ID1, ID2, Name, [Phone number], [last login], [email address]
2
  • What issues are you seeing with the code you have? Is it erroring, returning incorrect answers or null? etc... Jun 20, 2018 at 12:01
  • Hi @Gortonington sorry i got the error: An error occurred while checking the query syntax. Errors: Incorrect syntax near the keyword 'as'. From Data Extension is called "integration 2 play" Jun 20, 2018 at 12:59

2 Answers 2

3

It looks like just some syntax errors. As @Data_Kid pointed out there were issues with extra commas and you needed to bracket or double quote the DE name.

He was missing one item though, Max[Created Date] needed parenthesis around it to utilize the Max function.

Example:

SELECT ID1
, ID2
, Name
, [Phone number]
, [last login]
, MAX([created date]) as [created date]
, [email address]    
    FROM [integration 2play]           
    GROUP BY ID1
    , ID2
    , Name
    , [Phone number]
    , [last login]
    , [email address]
4
  • Snap!!+1 was stumped
    – 0xsegfault
    Jun 20, 2018 at 15:35
  • 1
    of course both of you :) @Data_Kid & Gortonington Jun 20, 2018 at 15:43
  • I have one more question. If iwould have a second "Date" field and i have to compare both and capture only the most recent date out of these two its ONLY archievalble by using CASE statement and when NULL value using COALESCE? Am i right on that? Jun 21, 2018 at 0:44
  • Like: SELECT ID1, ID2, Name, [Phone number], MAX[last login] as [last login], MAX[created date] as [created date], [email address] FROM [integration 2play] GROUP BY ID1, ID2, Name, [Phone number], WILL NOT WORK as 2 MAX statements are used? @Data_Kid :) Jun 21, 2018 at 0:52
2

You need to wrap the DE name in brackets [integration 2play] and take out the ,' from after [email address]

 SELECT ID1, ID2, Name, [Phone number], [last login], MAX[created date] as [created date], [email address]    
    FROM [integration 2play]
    WHERE [created date] >= DATEADD(dd,-1,getdate())

    GROUP BY ID1, ID2, Name, [Phone number], [last login], [email address]

Also you dont need the WHERE [created date] >= DATEADD(dd,-1,getdate()) since you are using the MAX(created date], it becomes redundant.

SELECT ID1
, ID2
, Name
, [Phone number]
, [last login]
, MAX[created date] as [created date]
, [email address]    
FROM [integration 2play]
GROUP BY ID1, ID2, Name, [Phone number], [last login], [email address]
3
  • thank you i changed it i now receive: An error occurred while checking the query syntax. Errors: Incorrect syntax near the keyword 'as'. Jun 20, 2018 at 13:06
  • also take out the ,' after the [email address]`
    – 0xsegfault
    Jun 20, 2018 at 13:08
  • thank you for the hints but sadly it still says: An error occurred while checking the query syntax. Errors: Incorrect syntax near the keyword 'as'. Jun 20, 2018 at 13:51

You must log in to answer this question.

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