SAP B1中合并两个SQL查询计算IsCommitted与Set Demand总和

问题核心原因 #

• 原写法的内层子查询未与外层查询的ItemCode做关联，会导致子查询返回多行数据触发报错，或所有行拿到错误的统一计算值
• 未处理无对应Set Demand记录的物料的空值情况，会导致求和结果变为NULL

正确实现方案 #

我们先通过CTE（公用表表达式）提前按物料编码聚合Set Demand的总数，再左关联到原仓库库存查询中，逻辑更清晰也避免子查询的关联错误：

-- 先聚合每个物料的总Set Demand
WITH SetDemandAgg AS (
    SELECT 
        T3.[ItemCode],
        SUM(-T3.[OnHand] + T1.[Quantity]) AS TotalSetDemand
    FROM [OITT] T0 WITH (NOLOCK) 
    INNER JOIN [ITT1] T1 WITH (NOLOCK) ON T0.[Code] = T1.[Father]
    CROSS JOIN [OSRI] T2 WITH (NOLOCK)
    INNER JOIN [OITW] T3 WITH (NOLOCK) ON T1.[Code] = T3.[ItemCode]
    INNER JOIN [OITM] T4 WITH (NOLOCK) ON T3.[ITEMCode] = T4.[ItemCode]
    WHERE 
        T0.[Code] = T2.[ItemCode] 
        AND T2.[IntrSerial] = T3.[WhsCode] 
        AND T2.[Status] <> 1 
        AND T2.[U_IsCon] <> 'YES' 
        AND T3.[OnHand] - T1.[Quantity] < 0 
        AND SUBSTRING(T2.[WhsCode],8,1)<>'C' 
        AND T2.[WhsCode] = '9000' 
        AND T4.[ItmsGrpCod] = 100
    GROUP BY T3.[ItemCode]
)
-- 关联原仓库查询计算总和
SELECT 
    T0.[ItemCode] AS 'Item No.',
    T0.[WhsCode] AS 'Warehouse Code',
    T0.[OnHand] AS 'In Stock',
    T0.[IsCommited] AS 'Committed',
    ISNULL(sda.TotalSetDemand, 0) AS 'Set Demand',
    -- 核心求和逻辑，无Set Demand时按0计算避免空值
    (T0.[IsCommited] + ISNULL(sda.TotalSetDemand, 0)) AS *Commited&SetDemand*,
    T0.[MinStock] AS 'Minimum Inventory',
    T0.[MaxStock] AS 'Maximum Inventory',
    T1.[ItmsGrpCod] AS 'Itemcode',
    T2.Price AS 'StandardCost'
FROM [OITW] T0 
INNER JOIN [OITM] T1 ON T0.ItemCode = T1.ItemCode 
LEFT JOIN [ITM1] T2 ON T1.ItemCode = T2.ItemCode AND T2.PriceList = 26
-- 左关联聚合后的Set Demand，保留所有原仓库查询的物料行
LEFT JOIN SetDemandAgg sda ON T0.ItemCode = sda.ItemCode
WHERE 
    (T0.[WhsCode] = N'9500') 
    AND (T1.[ItmsGrpCod] = N'100') 
    AND T0.[OnHand] > 0

改动说明 #

• 用CTE提前按ItemCode分组聚合所有物料的Set Demand总和，避免子查询重复计算
• 用LEFT JOIN关联聚合结果，没有对应Set Demand的物料会返回NULL，通过ISNULL转换为0不影响求和结果
• 保留了原查询的所有过滤条件和返回字段，同时新增了单独的Set Demand列方便核对数据

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