SQL查询WHERE子句参数设置：月度税额筛选语法错误排查

我有一份存储年度应纳税总额并按月拆分（字段如fcurJanuaryTotalDue、fcurFebruaryTotalDue等）的表，当收到用户某月度缴税通知（即“商机”，对应列fdtmFilingPeriod格式为dd-mm-yyyy）时，需要通过SQL查询生成商机并创建预估税付款，要求不生成金额低于100美元的预估付款。

SELECT  CONCAT  ('SUTRTN',
        '_',
        MONTH(p.fdtmFilingPeriod),
        '_',
        YEAR(p.fdtmFilingPeriod),
        '_',
        a.flngAccountKey) AS fstrRecordKey,
    i.fstrIdType,
    i.fstrId,
    a.flngCustomerKey,
    a.flngAccountKey,
    p.fdtmFilingPeriod,
    0.00 AS fcurAmount 

FROM    tblAccount a,
    tblID i,
    tblPeriod p,
    tblReturnExpectation re 

WHERE   a.flngVer           =  0 
AND a.fstrAccountType   =  'SUT' 
AND a.flngCustomerKey   =  i.flngCustomerKey 
AND a.flngAccountKey    =  i.flngAccountKey 
AND i.flngVer           =  0 
AND i.fstrIdType        =  'ACC' 
AND a.flngAccountKey    =  p.flngAccountKey 
AND p.flngVer           =  0 
AND p.fdtmFilingPeriod  >= @pdtmFilingPeriodFrom 
AND p.fdtmFilingPeriod  <= @pdtmFilingPeriodTo 
AND a.flngAccountKey    =  re.flngAccountKey 
AND p.fdtmFilingPeriod  =  re.fdtmFilingPeriod 
AND re.flngVer          =  0 
AND re.fstrStatus       =  'GNR' 
AND re.fdtmDue          <= DATEADD(dd, -45, @pdtmRunDate) 
AND EXISTS  (SELECT 1 

    FROM    tblSalesTaxReturnAmouts stra 

    WHERE   a.flngCustomerKey  = stra.flngCustomerKey 
    AND a.flngAccountKey   = stra.flngAccountKey 
    AND YEAR(re.fdtmFilingPeriod) = YEAR(stra.fdtmFilingPeriod)
    AND CASE
            When MONTH(stra.fdtmFilingPeriod) = 1 THEN fcurJanuaryTotalDue > 100
            When Month(stra.fdtmFilingPeriod) = 2 THEN fcurFebruaryTotalDUe > 100
            WHEN MONTH(re.fdtmFilingPeriod) = 3 THEN fcurMarchTotalDue > 100
            WHEN MONTH(re.fdtmFilingPeriod) = 4 THEN fcurAprilTotalDue > 100
            WHEN MONTH(re.fdtmFilingPeriod) = 5 THEN fcurMayTotalDue > 100
            WHEN MONTH(re.fdtmFilingPeriod) = 6 THEN fcurJuneTotalDue > 100
            WHEN MONTH(re.fdtmFilingPeriod) = 7 THEN fcurJulyTotalDue > 100
            WHEN MONTH(re.fdtmFilingPeriod) = 8 THEN fcurAugustTotalDue > 100
            WHEN MONTH(re.fdtmFilingPeriod) = 9 THEN fcurSeptemberTotalDue > 100
            WHEN MONTH(re.fdtmFilingPeriod) = 10 THEN fcurOctoberTotalDue > 100
            WHEN MONTH(re.fdtmFilingPeriod) = 11 THEN fcurNovemberTotalDue > 100
            WHEN MONTH(re.fdtmFilingPeriod) = 12 THEN fcurDecemberTotalDue > 100
        END
)

'Expecting '(' or Select statement before re.fdtmFilingPeriod
'>' on the first line has 'incorrect syntax.

语法错误原因 #

1. CASE表达式用法错误：SQL中CASE表达式不能直接返回布尔值（如fcurJanuaryTotalDue > 100），需将条件转换为可判断的逻辑结构。
2. EXISTS子句未闭合：原代码中EXISTS的子查询缺少闭合的)和语句结束符;。
3. 字段拼写不一致：混用MONTH(stra.fdtmFilingPeriod)和MONTH(re.fdtmFilingPeriod)，存在字段拼写错误（如fcurFebruaryTotalDUe应为fcurFebruaryTotalDue，tblSalesTaxReturnAmouts应为tblSalesTaxReturnAmounts）。
4. 旧式连接语法：使用逗号分隔表的写法易出错，建议改用显式JOIN语法。

修正后的SQL代码 #

SELECT  
    CONCAT('SUTRTN', '_', MONTH(p.fdtmFilingPeriod), '_', YEAR(p.fdtmFilingPeriod), '_', a.flngAccountKey) AS fstrRecordKey,
    i.fstrIdType,
    i.fstrId,
    a.flngCustomerKey,
    a.flngAccountKey,
    p.fdtmFilingPeriod,
    0.00 AS fcurAmount 
FROM    
    tblAccount a
    JOIN tblID i ON a.flngCustomerKey = i.flngCustomerKey AND a.flngAccountKey = i.flngAccountKey
    JOIN tblPeriod p ON a.flngAccountKey = p.flngAccountKey
    JOIN tblReturnExpectation re ON a.flngAccountKey = re.flngAccountKey AND p.fdtmFilingPeriod = re.fdtmFilingPeriod
WHERE   
    a.flngVer = 0 
    AND a.fstrAccountType = 'SUT' 
    AND i.flngVer = 0 
    AND i.fstrIdType = 'ACC' 
    AND p.flngVer = 0 
    AND p.fdtmFilingPeriod >= @pdtmFilingPeriodFrom 
    AND p.fdtmFilingPeriod <= @pdtmFilingPeriodTo 
    AND re.flngVer = 0 
    AND re.fstrStatus = 'GNR' 
    AND re.fdtmDue <= DATEADD(dd, -45, @pdtmRunDate) 
    AND EXISTS (
        SELECT 1 
        FROM tblSalesTaxReturnAmounts stra 
        WHERE 
            a.flngCustomerKey = stra.flngCustomerKey 
            AND a.flngAccountKey = stra.flngAccountKey 
            AND YEAR(re.fdtmFilingPeriod) = YEAR(stra.fdtmFilingPeriod)
            AND (
                (MONTH(re.fdtmFilingPeriod) = 1 AND stra.fcurJanuaryTotalDue > 100)
                OR (MONTH(re.fdtmFilingPeriod) = 2 AND stra.fcurFebruaryTotalDue > 100)
                OR (MONTH(re.fdtmFilingPeriod) = 3 AND stra.fcurMarchTotalDue > 100)
                OR (MONTH(re.fdtmFilingPeriod) = 4 AND stra.fcurAprilTotalDue > 100)
                OR (MONTH(re.fdtmFilingPeriod) = 5 AND stra.fcurMayTotalDue > 100)
                OR (MONTH(re.fdtmFilingPeriod) = 6 AND stra.fcurJuneTotalDue > 100)
                OR (MONTH(re.fdtmFilingPeriod) = 7 AND stra.fcurJulyTotalDue > 100)
                OR (MONTH(re.fdtmFilingPeriod) = 8 AND stra.fcurAugustTotalDue > 100)
                OR (MONTH(re.fdtmFilingPeriod) = 9 AND stra.fcurSeptemberTotalDue > 100)
                OR (MONTH(re.fdtmFilingPeriod) = 10 AND stra.fcurOctoberTotalDue > 100)
                OR (MONTH(re.fdtmFilingPeriod) = 11 AND stra.fcurNovemberTotalDue > 100)
                OR (MONTH(re.fdtmFilingPeriod) = 12 AND stra.fcurDecemberTotalDue > 100)
            )
    );

优化建议 #

如果后续需要扩展或维护，可将月度字段转换为行数据（使用UNPIVOT），避免大量OR条件，提升可读性：

-- 示例：将月度金额字段转为行结构
SELECT 
    stra.flngCustomerKey,
    stra.flngAccountKey,
    YEAR(stra.fdtmFilingPeriod) AS taxYear,
    amount
FROM tblSalesTaxReturnAmounts stra
UNPIVOT (
    amount FOR monthCol IN (
        fcurJanuaryTotalDue, fcurFebruaryTotalDue, fcurMarchTotalDue,
        fcurAprilTotalDue, fcurMayTotalDue, fcurJuneTotalDue,
        fcurJulyTotalDue, fcurAugustTotalDue, fcurSeptemberTotalDue,
        fcurOctoberTotalDue, fcurNovemberTotalDue, fcurDecemberTotalDue
    )
) AS unpvt
WHERE 
    CASE monthCol 
        WHEN 'fcurJanuaryTotalDue' THEN 1
        WHEN 'fcurFebruaryTotalDue' THEN 2
        WHEN 'fcurMarchTotalDue' THEN 3
        WHEN 'fcurAprilTotalDue' THEN 4
        WHEN 'fcurMayTotalDue' THEN 5
        WHEN 'fcurJuneTotalDue' THEN 6
        WHEN 'fcurJulyTotalDue' THEN 7
        WHEN 'fcurAugustTotalDue' THEN 8
        WHEN 'fcurSeptemberTotalDue' THEN 9
        WHEN 'fcurOctoberTotalDue' THEN 10
        WHEN 'fcurNovemberTotalDue' THEN 11
        WHEN 'fcurDecemberTotalDue' THEN 12
    END = MONTH(re.fdtmFilingPeriod)
    AND amount > 100;

内容的提问来源于stack exchange，提问作者Richard