协助访问 ADP 和/或 SQL iif 语句

Question

我正在使用Access ADP作为SQL的前端。我有两个表：

• 一是Price_2018
• 一是Price_2020

我想从Price_2018或Price_2020表中选择价格，具体取决于Purchase_Date。我尝试首先将 IiF 语句放入查询设计器中：

Iif ([Purchase_Date] < Convert(DateTime, '2020-01-01 00:00:00'),[Price_2018],[Price_2020])

Access 不允许这样做，最终将整个内容放在 SQL 窗格中的引号中，所以我得到的只是文本输出。

有人建议放入"选择"部分：

CASE ([Purchase_Date] < CONVERT(DATETIME, '2020-01-01 00:00:00')   WHEN 1 THEN [Price_2018] ELSE [Price_2020] 

这也不起作用，并给了我这个错误：

Error in list of function arguments: '<' not recognized.Error in list of function arguments: ',' not recognized.Error in list of function arguments: 'FROM' not recognized.
Unable to parse query text.

如何解决此错误。我不熟悉在 Access 或 SQL 中使用CASE语句。

我按照建议合并了 2 个价格表(称为 VWC_2018_2020 的组合表(。完整查询：

SELECT      TOP 100 PERCENT dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_ID,                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_Date_From, dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Update_Status,                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Account_Number, dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_Units,                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Modifiers, dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Procedure_Code,                         NormalUnionCPASumAdjustments.SumAmount AS Adjustment, NormalUnionCPASumPayments.SumAmount AS Payment,                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_Fee AS Charge,                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_Fee - ISNULL(NormalUnionCPASumPayments.SumAmount, 0) AS Unpaid,                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Primary_Diagnosis_Code, LastInsurancePmt.last_insurance_pmt,                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Patient_Number, dbo.VWCFees_2018_2020.VWC_2020, dbo.VWCFees_2018_2020.VWC_2018,                        IIF (dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_Date_From < CDate('2020-01-01 00:00:00'),dbo.VWCFees_2018_2020.VWC_2018,dbo.VWCFees_2018_2020.VWC_2020) AS VWCFEE
FROM          dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo LEFT OUTER JOIN                        dbo.VWCFees_2018_2020 ON dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Procedure_Code = dbo.VWCFees_2018_2020.CPT LEFT OUTER JOIN                        dbo.NormalUnionCPASumAdjustments() NormalUnionCPASumAdjustments ON                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_ID = NormalUnionCPASumAdjustments.Service_ID LEFT OUTER JOIN                        dbo.NormalUnionCPASumPayments() NormalUnionCPASumPayments ON                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_ID = NormalUnionCPASumPayments.Service_ID LEFT OUTER JOIN                        dbo.LastInsurancePmt() LastInsurancePmt ON dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_ID = LastInsurancePmt.Service_ID
WHERE      (dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Update_Status <= 1) AND                         (dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_Date_From BETWEEN @StartDate AND @EndDate) AND                         (dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Patient_Number = @VHC_NumberChild)

在 Access 中运行，会显示错误消息："<"无法识别。 缺少 FROM 子句。 无法分析查询文本。

现在怎么办？谢谢

Answer 1

由于 ADP 文件使用 SQL Server 后端(从 MS Access 2013 开始不再受支持(，因此必须使用其 TSQL 方言，该方言确实支持IIF(或CASE(，并且可以将日期与字符串表示形式进行比较而无需转换。

下面我将扩展到假设各种列的更完整的查询。根据需要进行调整：

SELECT
...
IIF(t.[Purchase_Date] < '2020-01-01', p18.price, p20.price)
...
FROM [Transactions] t
INNER JOIN [Price_2018] p18
ON t.price_id = p18.price_id
INNER JOIN [Price_2020] p20
ON t.price_id = p20.price_id