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.
| Author |
Topic |
|
rob41
Yak Posting Veteran
67 Posts |
 Posted - 2009-08-19 : 16:03:02
|
| I'm having trouble fixing the syntax. I've tried double clicking on the error message with little help. Below is a the code in question. Variance should be the difference between the two times. This query works in access 07 without a problem, converting it to SQL Server 05 is the tricky part :)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 Flag] = 1 THEN 0 END AS [CUDC], CASE WHEN [FP Flag] = 1 THEN 0 END AS [LCFP],CASE WHEN [CFAL] = 1 THEN 'Late' END AS [CFAL], CASE WHEN [MCAC]=1 THEN 'Late' END AS [MCAC], CASE WHEN [SH Arv D Loc Date]>[End Date]THEN 'Late' ELSE 0 END AS [LTL OT], CASE WHEN [MNMC]= 1 THEN 0 END AS [MNMC],CASE WHEN [Variance] > 0.010416667 THEN 'Late' END AS [XPD OT], CASE WHEN [Variance] > 0.166666667 THEN 'Late' END AS [TL OT] INTO [temp_Ontime_test]FROM [tblShipments];Msg 207, Level 16, State 1, Line 25Invalid column name 'Variance'.Msg 207, Level 16, State 1, Line 26Invalid column name 'Variance'. |
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-08-19 : 16:12:30
|
| You cannot reference your aliased column in your case statements. Try changing [Variance] to [SH Arv D Loc TS]-[End TS] instead.Some days you're the dog, and some days you're the fire hydrant. |
 |
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2009-08-19 : 16:13:08
|
| use actual fields in the case statement instead of the alias Variance |
|
|
|
|
|
|
|
|
Msg 207, Level 16, State 1, Line 25