query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query question

Author

Topic

rob41
Yak Posting Veteran

67 Posts

Posted - 2009-08-17 : 16:35:45

I am in the process of converting a query from Access 07 to SQL Server 05 there were a lot of IIf statements in the query. I have used the online help and I'm still coming up with an error. Below is the query I'm trying to convert.

SELECT
[tblShipments].[Load Id],
[tblShipments].[Shipment Id],
[tblShipments].[End Date],
[tblShipments].[SH Arv D Loc Date],
[tblShipments].[End TS],
[tblShipments].[SH Arv D Loc TS],
[SH Arv D Loc TS]-[End TS] AS Variance,
[tblShipments].[Conf Mode],
[tblShipments].[Transport Means Grp],
[tblShipments].[CUDC Flag] AS [CUDC],
[tblShipments].[XPD],
[tblShipments].[FP Flag] AS [LCFP],
[tblShipments].[CFAL],
[tblShipments].[CRTR],
[tblShipments].[MCAC],
[tblShipments].[MNMC],
CASE WHEN XPD = 1 OR CRTR = 1 THEN 1 ELSE 0 END as XPDFlag
CASE WHEN CUDC =1 THEN IS NULL END AS CUDC,
CASE WHEN LCFP =1 THEN IS NULL END AS LCFP,
CASE WHEN CFAL = 1 THEN Late ELSE Late END AS CFAL,
CASE WHEN MCAC = 1 THEN Late END AS MCAC,
CASE WHEN Transport Means Grp = TM2 END AS Transport Means Grp,
CASE WHEN SH Arv D Loc Date > [End Date] THEN Late ELSE IS NULL END AS [LTL OT],
CASE WHEN XPDFlag =1,
CASE WHEN MNMC =1 THEN IS NULL END AS MNMC,
CASE WHEN Variance > 0.010416667 THEN Late ELSE IS NULL END AS [XPD OT],
CASE WHEN Variance > 0.166666667 THEN Late ELSE IS NULL END AS [TL OT],
CASE WHEN OT = Late OR CFAL = 1 END AS OT,
CASE WHEN OT = Late OR MCAC =1 ESLE 0 END AS MC
INTO [temp_Ontime_test]
FROM [tblShipments];

Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'CASE'.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-17 : 16:39:14

CASE WHEN XPD = 1 OR CRTR = 1 THEN 1 ELSE 0 END as XPDFlag
there is a comma missing...

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-17 : 19:24:14

a missing comma in 1st case statement
and for 2nd, 3rd, 9th case statement what are you returning ? what do you want to do ?
and 6th & 7th case statement, enclose the column name in bracket [ ]
and 8th case statement is incomplete
and 10th and 11th case statement.

and 12th and 13th case statement, missing THEN


CASE WHEN XPD  = 1 OR CRTR = 1 THEN 1 ELSE 0 END as XPDFlag ,
CASE WHEN CUDC = 1 THEN IS NULL END AS CUDC,
CASE WHEN LCFP = 1 THEN IS NULL END AS LCFP,
CASE WHEN CFAL = 1 THEN Late ELSE Late END AS CFAL,
CASE WHEN MCAC = 1 THEN Late END AS MCAC,
CASE WHEN [Transport Means Grp] = TM2 END AS [Transport Means Grp],
CASE WHEN [SH Arv D Loc Date] > [End Date] THEN Late ELSE IS NULL END AS [LTL OT],
CASE WHEN XPDFlag =1,
CASE WHEN MNMC =1 THEN IS NULL END AS MNMC,
CASE WHEN Variance > 0.010416667 THEN Late ELSE IS NULL END AS [XPD OT],
CASE WHEN Variance > 0.166666667 THEN Late ELSE IS NULL END AS [TL OT],
CASE WHEN OT = Late OR CFAL = 1 THEN ? ? ? END AS OT,
CASE WHEN OT = Late OR MCAC = 1 THEN ? ? ? ESLE 0 END AS MC

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources