无法将Access查询转换为SQL Server [英] Trouble converting Access query to SQL Server
问题描述
这是我上个月一直在努力解决的问题。我的任务是将访问2007查询转换为SQL Server 2005.我已经能够转换与一个数据库相关的大量查询，现在我遇到了IIf的问题。我在线查看了这些书，并试图调试我正在研究的查询。此查询将成为其他查询前进的模型。有没有人成功地使用IIf并将其从Access 2007带到SQL Server 2005.
这里有几行代码和我得到的语法。
SELECT
[tblShipments]。[加载ID]，
[tblShipments]。[发货ID]，
[tblShipments]。[结束日期]，
[tblShipments]。[SH Arv D Loc日期]，
[tblShipments]。[结束TS]，
[tblShipments]。[SH Arv D Loc TS]，
[SH Arv D Loc TS] - [结束TS] AS差异，
[ 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当XPD = 1或CRTR = 1那么1 ELSE 0结束为XPDFlag
情况当CUDC = 1然后是空结束作为CUDC，
情况当LCFP = 1那么是空终止为LCFP，
情况当CFAL = 1那么晚ELSE晚结束为CFAL，
情况当MCAC = 1那么晚最后作为MCAC，
运输时的情况表示Grp = TM2 END作为运输工具意味着Grp，
情况当XPDFlag = 1，
情况当MNMC = 1那么它是NULL结束作为MNMC，
情况时的方差> 0.010416667然后，晚期ELSE为空终止[XPD OT]，
情况时的方差> 0.166666667那么，晚期ELSE为空结束[TL OT]，
情况当OT =延迟或CFAL = 1结束为OT，
情况当OT =晚期或MCAC = 1 ESLE 0 END AS MC
INTO [temp_Ontime_test]
来自[tblShipments];
消息156，等级15，状态1，行19
关键字''CASE''附近的语法不正确。
任何帮助都很有用。
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.
推荐答案
感谢您提供的有用提示I''mu现在唱出更正，我会告诉你它是怎么回事。
thanks for the helpful tips I''m using the corrections now and i''ll let you know how it went.
我修改了查询，最后我得到了以下错误。还有另一种在T SQL中表示空白值的方法吗？
SELECT
[加载ID]，
[货件标识]，
[结束日期]，
[SH Arv D Loc日期]，
[结束TS]，
[SH Arv D Loc TS]，
[SH Arv D Loc TS] - [结束TS] AS [方差]，
[Conf Mode]，
[运输方式Grp]，
[CUDC标志] AS [CUDC]，
[XPD]，
[ FP标志] AS [LCFP]，
[CFAL]，
[CRTR]，
[MCAC]，
[MNMC]，
情况当[XPD] = 1或[CRTR] = 1那么1 ELSE 0结束为XPDFlag，
情况[CUDC标志] = 1那么0结束[CUDC]，
情况[FP标志] = 1然后0结束[LCFP]，
情况[CFAL] = 1那么' '晚''结束为[CFAL]，
情况[MCAC] = 1那么''晚''结束为[MCAC]，
情况下[SH Arv D Loc Date]> [End Date] THEN''Late''ELSE NULL END AS [LTL OT]，
CASE WHEN [MNMC] = 1 THEN NULL END AS [MNMC]，
[方差]时的情况> 0.010416667那么'晚''结束为[XPD OT]，
情况[方差]> 0.166666667然后''晚''结束为[TL OT]
- 当[XPD] = 1或[CRTR] = 1然后[XPD OT]结束时
INTO [temp_Ontime_test]
FROM [tblShipments]
WHERE [OT] =''晚''
和[运输工具Grp] =' 'TM2''
消息8133，等级16，状态1，行1
CASE规范中的结果表达式都不是NULL。
Msg 207，Level 16，State 1，Line 26
无效的列名''Variance''。
Msg 207，Level 16，状态1，第27行
无效的列名称''Variance''。
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''.
这篇关于无法将Access查询转换为SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
