DB2中CASE WHEN内用IN子句报错，需统计连续两日订购产品数

直接错误原因 #

你收到的Comparison operator IN not valid报错，核心是CASE WHEN语法结构错误：你把THEN关键字写在了条件判断的括号内部，正确结构应为CASE WHEN (条件表达式) THEN 结果 END，错误写法破坏了语法规则，导致DB2无法解析。

如果仅修复原语法，调整后的单个统计项写法如下：

COUNT(
    DISTINCT CASE WHEN (
        CODPRO IN (SELECT DISTINCT CODPRO FROM FGE50NEUV1.GESUPD WHERE DATPRB1 = 20221027) 
        AND CODPRO IN (SELECT DISTINCT CODPRO FROM FGE50NEUV1.GESUPD WHERE DATPRB1 = 20221028)
    ) THEN CODPRO END
) AS NBCOMMUNVEILLE

但这种嵌套子查询的写法效率低下，且硬编码日期无法适配过去7天的动态需求，更优实现方案如下：

高效实现方案 #

先通过CTE提前筛选过去7天内所有连续两天被订购的产品及对应日期对，再关联到统计逻辑中：

WITH CTE AS (
    SELECT ALLPIC, COUNT(DISTINCT PAL.CODPRO) AS NBREFSSTOCKS
    FROM FGE50NEUV1.GEPAL PAL 
    INNER JOIN FGE50NEUV1.GEPIC PIC ON PAL.CODPRO = PIC.CODPRO
    GROUP BY ALLPIC
),
-- 生成过去7天的连续日期对，筛选同时在两天订购的产品
CONTINUOUS_PRODS AS (
    -- 昨天-前天
    SELECT 
        'YESTERDAY-PREV' AS DATE_GROUP,
        CODPRO,
        ALLSTS
    FROM FGE50NEUV1.GESUPD
    WHERE DATPRB1 = TO_CHAR(CURRENT_DATE - 1 DAY, 'YYYYMMDD')
    INTERSECT
    SELECT 
        'YESTERDAY-PREV' AS DATE_GROUP,
        CODPRO,
        ALLSTS
    FROM FGE50NEUV1.GESUPD
    WHERE DATPRB1 = TO_CHAR(CURRENT_DATE - 2 DAY, 'YYYYMMDD')
    UNION ALL
    -- 前天-大前天
    SELECT 
        'PREV-BEFORE_PREV' AS DATE_GROUP,
        CODPRO,
        ALLSTS
    FROM FGE50NEUV1.GESUPD
    WHERE DATPRB1 = TO_CHAR(CURRENT_DATE - 2 DAY, 'YYYYMMDD')
    INTERSECT
    SELECT 
        'PREV-BEFORE_PREV' AS DATE_GROUP,
        CODPRO,
        ALLSTS
    FROM FGE50NEUV1.GESUPD
    WHERE DATPRB1 = TO_CHAR(CURRENT_DATE - 3 DAY, 'YYYYMMDD')
    UNION ALL
    -- 大前天-4天前
    SELECT 
        'BEFORE_PREV-4DAYS_AGO' AS DATE_GROUP,
        CODPRO,
        ALLSTS
    FROM FGE50NEUV1.GESUPD
    WHERE DATPRB1 = TO_CHAR(CURRENT_DATE - 3 DAY, 'YYYYMMDD')
    INTERSECT
    SELECT 
        'BEFORE_PREV-4DAYS_AGO' AS DATE_GROUP,
        CODPRO,
        ALLSTS
    FROM FGE50NEUV1.GESUPD
    WHERE DATPRB1 = TO_CHAR(CURRENT_DATE - 4 DAY, 'YYYYMMDD')
    UNION ALL
    -- 4天前-5天前
    SELECT 
        '4DAYS_AGO-5DAYS_AGO' AS DATE_GROUP,
        CODPRO,
        ALLSTS
    FROM FGE50NEUV1.GESUPD
    WHERE DATPRB1 = TO_CHAR(CURRENT_DATE - 4 DAY, 'YYYYMMDD')
    INTERSECT
    SELECT 
        '4DAYS_AGO-5DAYS_AGO' AS DATE_GROUP,
        CODPRO,
        ALLSTS
    FROM FGE50NEUV1.GESUPD
    WHERE DATPRB1 = TO_CHAR(CURRENT_DATE - 5 DAY, 'YYYYMMDD')
    UNION ALL
    -- 5天前-6天前
    SELECT 
        '5DAYS_AGO-6DAYS_AGO' AS DATE_GROUP,
        CODPRO,
        ALLSTS
    FROM FGE50NEUV1.GESUPD
    WHERE DATPRB1 = TO_CHAR(CURRENT_DATE - 5 DAY, 'YYYYMMDD')
    INTERSECT
    SELECT 
        '5DAYS_AGO-6DAYS_AGO' AS DATE_GROUP,
        CODPRO,
        ALLSTS
    FROM FGE50NEUV1.GESUPD
    WHERE DATPRB1 = TO_CHAR(CURRENT_DATE - 6 DAY, 'YYYYMMDD')
    UNION ALL
    -- 6天前-7天前
    SELECT 
        '6DAYS_AGO-7DAYS_AGO' AS DATE_GROUP,
        CODPRO,
        ALLSTS
    FROM FGE50NEUV1.GESUPD
    WHERE DATPRB1 = TO_CHAR(CURRENT_DATE - 6 DAY, 'YYYYMMDD')
    INTERSECT
    SELECT 
        '6DAYS_AGO-7DAYS_AGO' AS DATE_GROUP,
        CODPRO,
        ALLSTS
    FROM FGE50NEUV1.GESUPD
    WHERE DATPRB1 = TO_CHAR(CURRENT_DATE - 7 DAY, 'YYYYMMDD')
),
CTE2 AS (
    SELECT 
        ALLSTS AS ALLPIC,
        COUNT(DISTINCT CASE WHEN DATPRB1 = ` + dateWMS() + ` THEN CODPRO END) AS NBREFSCDE,
        -- 统计各日期组的连续订购产品数
        COUNT(DISTINCT CASE WHEN DATE_GROUP = 'YESTERDAY-PREV' THEN CODPRO END) AS NBCOMMUNVEILLE,
        COUNT(DISTINCT CASE WHEN DATE_GROUP = 'PREV-BEFORE_PREV' THEN CODPRO END) AS NBCOMMUNJM2,
        COUNT(DISTINCT CASE WHEN DATE_GROUP = 'BEFORE_PREV-4DAYS_AGO' THEN CODPRO END) AS NBCOMMUNJM3,
        COUNT(DISTINCT CASE WHEN DATE_GROUP = '4DAYS_AGO-5DAYS_AGO' THEN CODPRO END) AS NBCOMMUNJM4,
        COUNT(DISTINCT CASE WHEN DATE_GROUP = '5DAYS_AGO-6DAYS_AGO' THEN CODPRO END) AS NBCOMMUNJM5,
        COUNT(DISTINCT CASE WHEN DATE_GROUP = '6DAYS_AGO-7DAYS_AGO' THEN CODPRO END) AS NBCOMMUNJM6,
        -- 统计过去7天内所有连续订购的产品总数
        COUNT(DISTINCT CODPRO) AS TOTAL_CONTINUOUS_PRODS
    FROM FGE50NEUV1.GESUPD SUP
    LEFT JOIN CONTINUOUS_PRODS CP ON SUP.ALLSTS = CP.ALLSTS AND SUP.CODPRO = CP.CODPRO
    GROUP BY ALLSTS
)
SELECT * 
FROM CTE 
INNER JOIN CTE2 ON CTE.ALLPIC = CTE2.ALLPIC 
ORDER BY CTE.ALLPIC

优化说明 #

1. 语法合规：确保CASE WHEN结构符合标准，避免语法错误。
2. 动态适配：用CURRENT_DATE - n DAY生成过去7天的连续日期，替代硬编码，满足动态统计需求。
3. 性能提升：通过INTERSECT提前筛选连续订购的产品，避免嵌套子查询的性能损耗，统计逻辑更清晰。

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