SAP SQL报表查询问题：U_Doc_Status为NULL的结果筛选失败

问题描述 #

在SAP中创建自定义字段U_Doc_Status，可选值为Delivered、Canceled，未填写时数据库存储为NULL。编写SQL生成报表时，已添加IS NULL筛选条件，但结果仍包含该字段有值的记录，原SQL代码如下：

SELECT DISTINCT 
    CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Dlvr_Date ) ELSE (SELECT T5.U_Dlvr_Date) END AS 'Delivery Date',
    T1.RefDate AS 'Posting Date', 
    CASE WHEN T1.TransType = '18' THEN (SELECT T4.ItemCode) ELSE (SELECT T6.ItemCode) END AS 'Item Code', 
    CASE WHEN T1.TransType = '18' THEN (SELECT T4.Dscription) ELSE (SELECT T6.Dscription) END AS 'Item Name',
    CASE WHEN T1.TransType = '18' THEN (SELECT T4.Quantity) ELSE (SELECT T6.Quantity) END AS 'Quantity',
    CASE WHEN T1.TransType = '18' THEN (SELECT CASE WHEN T4.UomCode = 'Manual' THEN 'KG' END AS 'UoM') ELSE (SELECT T6.UomCode) END AS 'UoM', 
    CASE T1.TransType WHEN 18 THEN 'A/P Invoice' WHEN 30 THEN 'Journal Entry'  WHEN 46 THEN 'Outgoing Payments' WHEN 60 THEN 'Goods Issue' END AS 'Document Source', 
    CASE WHEN T1.TransType = '18' THEN (SELECT T3.CardName) ELSE (SELECT T5.CardName) END AS 'Supplier Name' ,
    CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Itm_Src) ELSE (SELECT T5.U_Itm_Src) END AS 'Source', 
    CASE WHEN T1.TransType = '18' THEN (SELECT T1.BaseRef) ELSE (SELECT T5.U_Ref_No) END AS 'Reference Number', 
    CASE WHEN T1.TransType = '18'  THEN (SELECT T3.U_Itm_Orgn) ELSE (SELECT T5.U_Itm_Orgn) END AS 'Origins', 
    CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Dlvr_Frm) ELSE (SELECT T5.U_Dlvr_Frm) END AS 'Delivery From',
    CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Dlvr_Dest) ELSE (SELECT T5.U_Dlvr_Dest) END AS 'Destination', 
    CASE WHEN T1.TransType = '18' THEN (Select T3.U_Dlvr_Courier) ELSE (SELECT T5.U_Dlvr_Courier) END AS 'Courier',
    CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_awb_No) ELSE (SELECT T5.U_Awb_No)END AS 'AwB No', 
    CASE WHEN T1.TransType = '18' THEN (SELECT CAST (T3.U_QC_Rslt AS varchar (1200))) ELSE (SELECT CAST (T3.U_QC_Rslt AS varchar (1200))) END AS 'Test Result',
    CASE WHEN T1.TransType = '18' THEN (SELECT CAST (T3.U_Dlvr_Prp AS varchar (1200))) ELSE (SELECT CAST (T5.U_Dlvr_Prp AS varchar (1200))) END AS 'Purpose',
    T1.BaseRef AS 'Document Reference Number',
    CASE WHEN T1.TransType = '18' THEN (SELECT T3.U_Doc_Status) ELSE (SELECT T5.U_Doc_Status)END AS 'Document Status'
FROM OJDT T1
LEFT JOIN JDT1 T2 ON T1.TransID = T2.TransId
LEFT JOIN OPCH T3 ON T1.BaseRef =  T3.DocNum
LEFT JOIN PCH1 T4 ON T3.DocEntry = T4.DocEntry
LEFT JOIN OIGE T5 ON T1.BaseRef = T5.DocNum
LEFT JOIN IGE1 T6 ON T6.DocEntry = T5.DocEntry
LEFT JOIN IBT1 T7 ON T6.ObjType = T7.BaseType and T5.DocEntry = T7.BaseEntry and T6.LineNum = T7.BaseLinNum and T6.ItemCode = T7.ItemCode
LEFT JOIN OPOR T8 ON T7.BatchNum = T8.DocNum
WHERE T2.Account = '610218' AND T1.RefDate BETWEEN '2023-02-01' AND '2023-12-31' AND T3.U_Doc_Status + T5.U_Doc_Status IS NULL
ORDER BY T1.RefDate

错误原因分析 #

原WHERE子句中的T3.U_Doc_Status + T5.U_Doc_Status IS NULL逻辑存在严重问题：

• 字符串拼接操作中，只要参与拼接的任意一个值为NULL，拼接结果就会是NULL。这意味着只要T3或T5其中一个表的U_Doc_Status为NULL，不管另一个表的该字段是否有值，条件都会被判定为真，完全达不到筛选出对应业务表中U_Doc_Status为NULL的目的。
• 正确逻辑应该是根据T1.TransType的取值，判断对应的主业务表（OPCH或OIGE）中的U_Doc_Status是否为NULL。

修正方案 #

1. 修复WHERE筛选条件：根据TransType的值，分别判断对应表的U_Doc_Status是否为NULL
2. 优化SELECT子查询：去掉不必要的嵌套子查询，直接引用字段值，提升查询效率

修正后的完整SQL代码：

SELECT DISTINCT 
    CASE WHEN T1.TransType = '18' THEN T3.U_Dlvr_Date ELSE T5.U_Dlvr_Date END AS 'Delivery Date',
    T1.RefDate AS 'Posting Date', 
    CASE WHEN T1.TransType = '18' THEN T4.ItemCode ELSE T6.ItemCode END AS 'Item Code', 
    CASE WHEN T1.TransType = '18' THEN T4.Dscription ELSE T6.Dscription END AS 'Item Name',
    CASE WHEN T1.TransType = '18' THEN T4.Quantity ELSE T6.Quantity END AS 'Quantity',
    CASE WHEN T1.TransType = '18' THEN CASE WHEN T4.UomCode = 'Manual' THEN 'KG' END ELSE T6.UomCode END AS 'UoM', 
    CASE T1.TransType WHEN 18 THEN 'A/P Invoice' WHEN 30 THEN 'Journal Entry'  WHEN 46 THEN 'Outgoing Payments' WHEN 60 THEN 'Goods Issue' END AS 'Document Source', 
    CASE WHEN T1.TransType = '18' THEN T3.CardName ELSE T5.CardName END AS 'Supplier Name' ,
    CASE WHEN T1.TransType = '18' THEN T3.U_Itm_Src ELSE T5.U_Itm_Src END AS 'Source', 
    CASE WHEN T1.TransType = '18' THEN T1.BaseRef ELSE T5.U_Ref_No END AS 'Reference Number', 
    CASE WHEN T1.TransType = '18' THEN T3.U_Itm_Orgn ELSE T5.U_Itm_Orgn END AS 'Origins', 
    CASE WHEN T1.TransType = '18' THEN T3.U_Dlvr_Frm ELSE T5.U_Dlvr_Frm END AS 'Delivery From',
    CASE WHEN T1.TransType = '18' THEN T3.U_Dlvr_Dest ELSE T5.U_Dlvr_Dest END AS 'Destination', 
    CASE WHEN T1.TransType = '18' THEN T3.U_Dlvr_Courier ELSE T5.U_Dlvr_Courier END AS 'Courier',
    CASE WHEN T1.TransType = '18' THEN T3.U_awb_No ELSE T5.U_Awb_No END AS 'AwB No', 
    CASE WHEN T1.TransType = '18' THEN CAST(T3.U_QC_Rslt AS VARCHAR(1200)) ELSE CAST(T5.U_QC_Rslt AS VARCHAR(1200)) END AS 'Test Result',
    CASE WHEN T1.TransType = '18' THEN CAST(T3.U_Dlvr_Prp AS VARCHAR(1200)) ELSE CAST(T5.U_Dlvr_Prp AS VARCHAR(1200)) END AS 'Purpose',
    T1.BaseRef AS 'Document Reference Number',
    CASE WHEN T1.TransType = '18' THEN T3.U_Doc_Status ELSE T5.U_Doc_Status END AS 'Document Status'
FROM OJDT T1
LEFT JOIN JDT1 T2 ON T1.TransID = T2.TransId
LEFT JOIN OPCH T3 ON T1.BaseRef = T3.DocNum
LEFT JOIN PCH1 T4 ON T3.DocEntry = T4.DocEntry
LEFT JOIN OIGE T5 ON T1.BaseRef = T5.DocNum
LEFT JOIN IGE1 T6 ON T6.DocEntry = T5.DocEntry
LEFT JOIN IBT1 T7 ON T6.ObjType = T7.BaseType AND T5.DocEntry = T7.BaseEntry AND T6.LineNum = T7.BaseLinNum AND T6.ItemCode = T7.ItemCode
LEFT JOIN OPOR T8 ON T7.BatchNum = T8.DocNum
WHERE T2.Account = '610218' 
  AND T1.RefDate BETWEEN '2023-02-01' AND '2023-12-31'
  AND (
    (T1.TransType = '18' AND T3.U_Doc_Status IS NULL)
    OR 
    (T1.TransType != '18' AND T5.U_Doc_Status IS NULL)
  )
ORDER BY T1.RefDate

额外说明 #

• 原SELECT语句中嵌套的(SELECT 字段)完全多余，直接引用关联表的字段即可，避免不必要的子查询开销
• 修正后的WHERE条件确保：当TransType=18时只筛选OPCH表中U_Doc_Status为NULL的记录，其他类型则筛选OIGE表中对应字段为NULL的记录

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