August 18, 2009 at 2:20 pm
I'm trying to complete the conversion of an access 07 query to a sql server 05 query. The access query had IIf which I changed to CASE WHEN. Code parses fine and then when I execute it I get errors like
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'END'.
Does anyone have an example of a query with multiple case when statements?
August 18, 2009 at 2:24 pm
Want to post your piece of code, we'll check for the error.
Cheers,
J-F
August 18, 2009 at 2:26 pm
It's hard to help without more information. As requested, please post your query. In addition to that, sample data and an example of what your result set should look like would help.
http://www.sqlservercentral.com/articles/Best+Practices/61537/"> http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 18, 2009 at 2:32 pm
Here's the query
SELECT [Load Id],
[Shipment Id],
[End Date],
[SH Arv D Loc Date],
[End TS],
[SH Arv D Loc TS],
[SH Arv D Loc TS]-[End TS] AS Variance,
[Conf Mode],
[Transport Means Grp],
[CUDC Flag] AS [CUDC],
[[XPD],
[FP Flag] AS [LCFP],
[CFAL],
[CRTR],
[MCAC],
[MNMC],
CASE WHEN [XPD] = 1 OR [CRTR] = 1 THEN 1 ELSE 0 END as XPDFlag,
CASE WHEN [CUDC] = 1 THEN NULL END,
CASE WHEN [LCFP] = 1 THEN NULL END,
CASE WHEN [CFAL] = 1 THEN "Late" END,
CASE WHEN [MCAC]=1 THEN "Late" END,
CASE WHEN [Transport Means Grp] = TM2 END,
CASE WHEN [SH Arv D Loc Date]>[End Date]THEN "Late" ELSE NULL AS [LTL OT]END,
CASE WHEN [XPD Flag]= 1 END,
CASE WHEN [MNMC]= 1 THEN NULL END,
CASE WHEN [XPD Flag] = 1 END,
CASE WHEN Variance > 0.010416667 THEN "Late" ELSE NULL AS [XPD OT]END,
CASE WHEN [CUDC]= 1 THEN NULL END,
CASE WHEN [LCFP]= 1 THEN NULL END,
CASE WHEN [CFAL]= 1 THEN "Late" END,
CASE WHEN [MCAC]= 1 THEN "Late" END,
CASE WHEN [Transport Means Grp] "TM2" END,
CASE WHEN Variance > 0.166666667 THEN "Late" ELSE NULL AS [TL OT] END,
CASE WHEN [XPD]= 1 Or [CRTR] = 1 THEN [XPD OT] END,
CASE WHEN [Transport Means Grp] = "TM2" THEN [LTL OT] ELSE [TL OT] AS OT END,
CASE WHEN [OT] = "Late" And [CFAL] =1 END,
CASE WHEN [OT] = "Late" And [MCAC]= 1 THEN 0 AS MC END
INTO [temp_Ontime_test]
FROM [tblShipments];
August 18, 2009 at 2:40 pm
I corrected a few syntax errors you had, but I cannot test it.
You need to put the syntax that way
Select CASE WHEN a = B then X else Y end AS Nbr1
You always need at least a "Then X", and the AS must always be after the end of the case.. I think it will work.
SELECT [Load Id],
[Shipment Id],
[End Date],
[SH Arv D Loc Date],
[End TS],
[SH Arv D Loc TS],
[SH Arv D Loc TS]-[End TS] AS Variance,
[Conf Mode],
[Transport Means Grp],
[CUDC Flag] AS [CUDC],
[[XPD],
[FP Flag] AS [LCFP],
[CFAL],
[CRTR],
[MCAC],
[MNMC],
CASE WHEN [XPD] = 1 OR [CRTR] = 1 THEN 1 ELSE 0 END as XPDFlag,
CASE WHEN [CUDC] = 1 THEN NULL END,
CASE WHEN [LCFP] = 1 THEN NULL END,
CASE WHEN [CFAL] = 1 THEN "Late" END,
CASE WHEN [MCAC]=1 THEN "Late" END,
CASE WHEN [Transport Means Grp] = TM2 THEN 1 END, /* When it equals TM2, then what? you need CASE WHEN [Transport Means Grp] = TM2 THEN ... END */
CASE WHEN [SH Arv D Loc Date]>[End Date]THEN "Late" ELSE NULL END AS [LTL OT],
CASE WHEN [XPD Flag]= 1 then 1 END, -- same here
CASE WHEN [MNMC]= 1 THEN NULL END,
CASE WHEN [XPD Flag] = 1 THEN 1 END, -- Same here
CASE WHEN Variance > 0.010416667 THEN "Late" ELSE NULL END AS [XPD OT],
CASE WHEN [CUDC]= 1 THEN NULL END,
CASE WHEN [LCFP]= 1 THEN NULL END,
CASE WHEN [CFAL]= 1 THEN "Late" END,
CASE WHEN [MCAC]= 1 THEN "Late" END,
CASE WHEN [Transport Means Grp] "TM2" then 1 END, -- Same here
CASE WHEN Variance > 0.166666667 THEN "Late" ELSE NULL END AS [TL OT],
CASE WHEN [XPD]= 1 Or [CRTR] = 1 THEN [XPD OT] END,
CASE WHEN [Transport Means Grp] = "TM2" THEN [LTL OT] ELSE [TL OT] END AS OT , -- THE "AS OT" was before the END here.
CASE WHEN [OT] = "Late" And [CFAL] =1 then 1 END, -- Again
CASE WHEN [OT] = "Late" And [MCAC]= 1 THEN 0 END AS MC
INTO [temp_Ontime_test]
FROM [tblShipments];
Edit : Corrected weird commenting glitchs.
Cheers,
J-F
August 18, 2009 at 2:47 pm
If you double click on the error message SSMS will sometimes take you straight to the problem. It works in this case. Look at the sixth CASE statement down, you don't have a THEN clause for the CASE statement.
Edit: Noticed that there were additional errors, but you might give the double click on the error message a try before jumping to the forum. 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply