Trouble converting Access query to SQL Server

[Anthony97]

This is a problem I've been fighting through for the last month. I've been tasked with converting access 2007 queries to SQL Server 2005. I have been able to convert a number of queries associated with one data base now I'm running into problems with IIf. I've looked at the books online and tried to debug the query I'm working on now. This query will be a model for other queries moving forward. Has anyone had success working with IIf and taking it from Access 2007 to SQL Server 2005.

Here are a few lines of the code and the syntax I'm getting.

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'.

Any help is greatly appreicated.

[Delerna]

IIF(condition,true action,false action)
converts to
CASE WHEN condition THEN true action ELSE false action END
 

Here is one of your syntax errors
CASE WHEN CUDC =1 THEN IS NULL END AS CUDC

not sure what you are tring to do here but perhaps
CASE WHEN CUDC =1 THEN NULL END AS CUDC

Incidentally, that line as it is will always return CUCD=null because there is no ELSE
so
null as CUCD
would do the same thing.




ANOTHER
CASE WHEN CFAL = 1 THEN Late ELSE Late END AS CFAL

CFAL always equals Late so why not

Late as CFAL

?



ANOTHER

CASE WHEN OT = Late OR CFAL = 1 END AS OT
Huh....its not doing anything



ONE MORE
CASE WHEN Transport Means Grp = TM2 END AS Transport Means Grp

field names with spaces must be enclosed in []
CASE WHEN [Transport Means Grp] = TM2 END AS [Transport Means Grp]
and its not doing anything
you are saying IIF(condition) as FieldName




Keep at it you will get it right in the end :)

[Anthony97]

thanks for the helpful tips I'm using the corrections now and i'll let you know how it went.

[Anthony97]

I modified the query and I'm getting the following errors at the end. Is there another way to represent a blank value in T SQL ?

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 NULL END AS [LTL OT],
CASE WHEN [MNMC]= 1 THEN NULL 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]
--CASE WHEN [XPD]= 1 Or [CRTR] = 1 THEN [XPD OT] END
INTO [temp_Ontime_test]
FROM [tblShipments]
WHERE [OT] = 'Late'
AND [Transport Means Grp] = 'TM2'


Msg 8133, Level 16, State 1, Line 1
None of the result expressions in a CASE specification can be NULL.
Msg 207, Level 16, State 1, Line 26
Invalid column name 'Variance'.
Msg 207, Level 16, State 1, Line 27
Invalid column name 'Variance'.

[Anthony97]

is there any tool that quickly converts access 07 to sql server 05?

[Delerna]

A Tool...probably, google for it.
I have never bothered because conversion is not that difficult once you learn the rules.

In access you can reference a field that is the result of a calculation
In your query Variance is a calculated field.

In SQL server you cannot do that. There is no field in tblShipments called Variance and so it complains.

Instead of this
CASE WHEN [Variance] > 0.010416667 THEN 'Late' END AS [XPD OT],

you need to do this
CASE WHEN [SH Arv D Loc TS]-[End TS] > 0.010416667 THEN 'Late' END AS [XPD OT],

[Delerna]

None of the result expressions in a CASE specification can be NULL.

Yes I get that same error if I do similar to this
CASE WHEN [MNMC]= 1 THEN NULL END AS [MNMC],

or this
CASE WHEN [MNMC]= 1 THEN NULL ELSE NULL END AS [MNMC],

but this is OK
CASE WHEN [MNMC]= 1 THEN NULL ELSE 0 END AS [MNMC],

Anyway, I don't really understand what you are trying to do here.
What is the point of returning NULL if MNMC=1 ?
and what should be returned if MNMC<>1?

[wonn1377]

I prefer a third party applpications for migrating my data, i use data loader when i was migrating access to SQL it work great, and it can migrate almost any database.


Download Free : http://www.dbload.com

[Anthony97]

Thanks I'll check it out, will this work with the queries as well, that's my major area of concern.

[wonn1377]

"Anthiny97"
ya it will b helpful for you...

[pluciorx]

hi i was suffering some simillar problems
most of the code query can be easy translated
by tool like: http://www.dpriver.com/pp/sqlformat.htm ( select source as MSACCESS)
but You must remember about enviroment specific keyword such as Case , when. etc.IIF