How to fix syntax END AS in SQL?

Question

Another syntax error is showing up in my query as below (error in the last paragraph). My first post related to this query was fixed after adding a missing "CASE" to it but this one appeared after I tried to execute it.

CASE WHEN CONVERT(date,rd.F254) Between CONVERT(date,getdate()-7) AND CONVERT(date, getdate()-1) THEN rd.F64 
END AS QtyLastWeek,
CASE WHEN CONVERT(date,rd.F254) <= CONVERT(date,getdate()-8) THEN rd.F64 
END AS Qty2WeeksAgo
INTO idb_datastore.dbo.[Testing]
FROM  SMSSERVER01.STORESQL.dbo.OBJ_TAB ob
        INNER JOIN SMSSERVER01.STORESQL.dbo.LOC_TAB lo ON ob.F01 = lo.F01 
      INNER JOIN SMSSERVER01.STORESQL.dbo.COST_TAB co ON ob.F01 = co.F01 
    INNER JOIN SMSSERVER01.STORESQL.dbo.RPT_ITM_D rd ON ob.F01 = rd.F01 
  INNER JOIN SMSSERVER01.STORESQL.dbo.CAT_TAB ca ON ob.F17 = ca.F17 
WHERE rd.F254 >=getDate()-14 AND co.F90 = 1 AND co.F08 IS NULL AND F1034 = 3
GROUP BY lo.F117,
     ob.F01,
     ob.F155,
     ob.F29,
     ob.F22,
     ca.F1023,
     co.F90,
     co.F08,
     co.F27,
     rd.F1034,
     CASE WHEN CONVERT(date,rd.F254) Between CONVERT(date,getdate()-7) AND CONVERT(date, getdate()-1) THEN rd.F64 END AS QtyLastWeek
    CASE WHEN CONVERT(date, rd.F254) <= CONVERT(date,getdate()-8) THEN 
    rd.F64 END AS Qty2WeeksAgo

Why are you asking this question again? Didn't the answer on the first question help you? — Tab Alleman, Feb 06 '19 at 19:50
Dude, this is a different question, not even the same function. Read first before marking it as duplicate maybe? — Lionel Kirk, Feb 06 '19 at 20:09
Can you point out the difference to me? I'm missing it somehow... — Tab Alleman, Feb 06 '19 at 20:10
The previous one was a syntax error on "WHEN" (line 22), which I have corrected and executed. Now I am getting a syntax error at the bottom of the query (line 44) near "AS". What else do you need? — Lionel Kirk, Feb 06 '19 at 20:15
But the code in the two questions is the same, and the reason for the error is the same. Did you try the fix from the first question on the code in the second question? — Tab Alleman, Feb 06 '19 at 20:26
The first question was for the first part of the code, it was only pointing out this error (WHEN) for the whole query. I changed it according to the help received earlier and now this new syntax error shows up under a different keyword. — Lionel Kirk, Feb 06 '19 at 20:30
And I have just amended the code as per the 2 answers below, the same syntax error is still there. What can I do with my 'duplicated' issue now? — Lionel Kirk, Feb 06 '19 at 20:35
Edit your question and add the code as you now have it, and include some of the code before and after, because the actual error could be on a line before or after where SQL Server says it is. The code in the two answers below are both error-free, so if you get an error with those answers, then the error is on a different line in the query. — Tab Alleman, Feb 06 '19 at 20:57
Noted. Should I just edit this post or create a new one? Can other users see it even though it is marked as a duplicate? — Lionel Kirk, Feb 07 '19 at 13:37

score 1 · Answer 1 · answered Feb 07 '19 at 14:51

1

I'm not sure if this is what is causing the error, but you don't need the AS Alias in your GROUP BY clause. You only need it in the SELECT list.

So at the bottom of your query (after GROUP BY), you can remove AS QtyLastWeek and AS Qty2WeeksAgo

If that doesn't solve the problem, then the error must be before the part of the query you are now showing in your question. I see no errors in the rest of the code you are currently showing.

answered Feb 07 '19 at 14:51

Tab Alleman

31,483
7
36
52

I see same thing. Need to remove Alias and add comma to indicate end of column in GROUP by – NonProgrammer Feb 07 '19 at 14:56
Oh yes, the missing comma will definitely cause an error, good catch @NonProgrammer – Tab Alleman Feb 07 '19 at 14:56
Just amended and executed, the error message is now: Msg 156, Level 15, State 1, Procedure [...], Line 45 Incorrect syntax near the keyword 'CASE'. – Lionel Kirk Feb 07 '19 at 16:35
Did you correct the missing comma after the first CASE in the GROUP BY? Can you post the code that is currently generating the error? – Tab Alleman Feb 07 '19 at 17:04
Yep, I removed the AS Alias and added the comma. Here is the latest error: Msg 8120, Level 16, State 1, Procedure idb_dataSTORESQLTesting, Line 18 Column 'SMSSERVER01.STORESQL.dbo.RPT_ITM_D.F64' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I guess the alias needs to be there. – Lionel Kirk Feb 07 '19 at 20:57
https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e – Tab Alleman Feb 07 '19 at 20:58

score 0 · Answer 2 · answered Feb 06 '19 at 19:48

0

If you need two column you need two case

CASE WHEN CONVERT(date,rd.F254) 
    Between CONVERT(date,getdate()-7) AND CONVERT(date, getdate()-1) 
    THEN rd.F64 END AS QtyLastWeek,
CASE WHEN CONVERT(date, rd.F254) <= CONVERT(date,getdate()-8) 
    THEN rd.F64 END AS Qty2WeeksAgo

answered Feb 06 '19 at 19:48

ScaisEdge

131,976
10
91
107

then you should update your question and add the complete query code – ScaisEdge Feb 06 '19 at 20:57

score 0 · Answer 3 · answered Feb 06 '19 at 19:49

you missded case for second comparison

CASE WHEN CONVERT(date,rd.F254) Between CONVERT(date,getdate()-7) AND CONVERT(date, getdate()-1) THEN rd.F64 END AS QtyLastWeek,

case WHEN CONVERT(date, rd.F254) <= CONVERT(date,getdate()-8) THEN rd.F64 END AS Qty2WeeksAgo

How to fix syntax END AS in SQL?

3 Answers3