GROUP BY子句中CASE WHEN语句语法错误求助

错误原因 #

你遇到的语法错误核心是GROUP BY子句中的CASE表达式缺少结束关键字END，另外重复书写冗长的CASE表达式不仅容易出错，还会降低代码可读性。

正确写法方案 #

方案1：补全GROUP BY中的CASE表达式 #

直接在GROUP BY的CASE末尾添加END，确保语法完整：

SELECT
    CASE 
        WHEN OH.CreditOverride = 1 THEN 'n/a'
        WHEN OH.OrderHeld = 1 AND C.CreditHold = 0 AND C.AgingCreditHold = 0 AND OH.CreditOverride = 0 THEN 'HeldOrder'
        WHEN C.CreditHold = 1 AND OH.OrderHeld = 0 AND C.AgingCreditHold = 0 AND OH.CreditOverride = 0 THEN 'CrHold'
        WHEN C.AgingCreditHold = 1 AND C.CreditHold = 0 AND OH.OrderHeld = 0 AND OH.CreditOverride = 0 THEN 'AgingHold'
        WHEN OH.OrderHeld = 1 AND C.CreditHold = 1 AND C.AgingCreditHold = 0 AND OH.CreditOverride = 0 THEN 'HeldOrder_CrHold'
        WHEN OH.OrderHeld = 1 AND C.CreditHold = 0 AND C.AgingCreditHold = 1 AND OH.CreditOverride = 0 THEN 'HeldOrder_AgingHold'
        WHEN C.CreditHold = 1 AND OH.OrderHeld = 0 AND C.AgingCreditHold = 1 AND OH.CreditOverride = 0 THEN 'CrHold_AgingHold'
        ELSE 'n/a'
    END AS HeldReason,
    SUM(CASE WHEN P.PartsPerContainer <> 0 THEN FLOOR(OD.OrderQty / P.PartsPerContainer) ELSE 0 END) AS [Number of Pallets to pick],
    SUM(CASE WHEN P.PartsPerContainer <> 0 THEN (OD.OrderQty % P.PartsPerContainer) ELSE 0 END) AS [Number of cases to Picks],
    SUM(CASE WHEN P.PartsPerContainer <> 0 THEN FLOOR(OD.OrderQty / P.PartsPerContainer) + (OD.OrderQty % P.PartsPerContainer) ELSE 0 END) AS [Total Number of Picks Per Order]
FROM erp.OrderHed AS OH
-- 补充你的JOIN关联语句（比如JOIN erp.OrderDtl OD ON OH.OrderNum = OD.OrderNum等）
-- 补充你的WHERE过滤条件
GROUP BY
    CASE 
        WHEN OH.CreditOverride = 1 THEN 'n/a'
        WHEN OH.OrderHeld = 1 AND C.CreditHold = 0 AND C.AgingCreditHold = 0 AND OH.CreditOverride = 0 THEN 'HeldOrder'
        WHEN C.CreditHold = 1 AND OH.OrderHeld = 0 AND C.AgingCreditHold = 0 AND OH.CreditOverride = 0 THEN 'CrHold'
        WHEN C.AgingCreditHold = 1 AND C.CreditHold = 0 AND OH.OrderHeld = 0 AND OH.CreditOverride = 0 THEN 'AgingHold'
        WHEN OH.OrderHeld = 1 AND C.CreditHold = 1 AND C.AgingCreditHold = 0 AND OH.CreditOverride = 0 THEN 'HeldOrder_CrHold'
        WHEN OH.OrderHeld = 1 AND C.CreditHold = 0 AND C.AgingCreditHold = 1 AND OH.CreditOverride = 0 THEN 'HeldOrder_AgingHold'
        WHEN C.CreditHold = 1 AND OH.OrderHeld = 0 AND C.AgingCreditHold = 1 AND OH.CreditOverride = 0 THEN 'CrHold_AgingHold'
        ELSE 'n/a'
    END -- 必须添加END，否则语法不完整

方案2：使用CTE简化代码（推荐） #

通过公共表表达式（CTE）先计算出HeldReason，再在主查询中分组，避免重复书写CASE表达式，可读性和可维护性更强：

WITH OrderHeldReasons AS (
    SELECT
        CASE 
            WHEN OH.CreditOverride = 1 THEN 'n/a'
            WHEN OH.OrderHeld = 1 AND C.CreditHold = 0 AND C.AgingCreditHold = 0 AND OH.CreditOverride = 0 THEN 'HeldOrder'
            WHEN C.CreditHold = 1 AND OH.OrderHeld = 0 AND C.AgingCreditHold = 0 AND OH.CreditOverride = 0 THEN 'CrHold'
            WHEN C.AgingCreditHold = 1 AND C.CreditHold = 0 AND OH.OrderHeld = 0 AND OH.CreditOverride = 0 THEN 'AgingHold'
            WHEN OH.OrderHeld = 1 AND C.CreditHold = 1 AND C.AgingCreditHold = 0 AND OH.CreditOverride = 0 THEN 'HeldOrder_CrHold'
            WHEN OH.OrderHeld = 1 AND C.CreditHold = 0 AND C.AgingCreditHold = 1 AND OH.CreditOverride = 0 THEN 'HeldOrder_AgingHold'
            WHEN C.CreditHold = 1 AND OH.OrderHeld = 0 AND C.AgingCreditHold = 1 AND OH.CreditOverride = 0 THEN 'CrHold_AgingHold'
            ELSE 'n/a'
        END AS HeldReason,
        OD.OrderQty,
        P.PartsPerContainer
    FROM erp.OrderHed AS OH
    -- 补充你的JOIN关联语句，例如：
    -- JOIN erp.OrderDtl OD ON OH.OrderNum = OD.OrderNum
    -- JOIN erp.Customer C ON OH.CustNum = C.CustNum
    -- JOIN erp.Part P ON OD.PartNum = P.PartNum
    -- 补充你的WHERE过滤条件
)
SELECT
    HeldReason,
    SUM(CASE WHEN PartsPerContainer <> 0 THEN FLOOR(OrderQty / PartsPerContainer) ELSE 0 END) AS [Number of Pallets to pick],
    SUM(CASE WHEN PartsPerContainer <> 0 THEN (OrderQty % PartsPerContainer) ELSE 0 END) AS [Number of cases to Picks],
    SUM(CASE WHEN PartsPerContainer <> 0 THEN FLOOR(OrderQty / PartsPerContainer) + (OrderQty % PartsPerContainer) ELSE 0 END) AS [Total Number of Picks Per Order]
FROM OrderHeldReasons
GROUP BY HeldReason;

额外说明 #

• 你提供的代码中From > ...部分不完整，需要补充完整的表关联逻辑（JOIN语句）才能正常执行。
• 部分数据库（如SQL Server）支持在GROUP BY中直接使用SELECT子句的列别名（如GROUP BY HeldReason），但CTE写法的兼容性更好，适合多数据库环境。

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