PO行项关联GRPO状态判断的SQL语句优化需求

原查询的核心问题是CASE语句逻辑错误：你现在只检查集装箱号是否存在，而非该集装箱号对应的PO行是否已经关联了GRPO（收货单）。以下是修正后的查询逻辑：

方法1：用EXISTS子查询（性能更优） #

SELECT
    OPOR.DocNum AS PO_Document_Number,
    POR1.U_ContainerNo AS Container_Number,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM OPDN
            INNER JOIN PDN1 ON OPDN.DocEntry = PDN1.DocEntry
            WHERE PDN1.BaseEntry = OPOR.DocEntry
              AND PDN1.BaseLine = POR1.LineNum
              -- 可选：若需GRPO与PO行集装箱号一致才判定收货，添加此行
              -- AND PDN1.U_ContainerNo = POR1.U_ContainerNo
        ) THEN 'Shipment Received'
        ELSE 'No Shipment Received'
    END AS Shipment_Status
FROM
    OPOR
INNER JOIN POR1 ON POR1.DocEntry = OPOR.DocEntry
GROUP BY
    OPOR.DocNum, POR1.U_ContainerNo, POR1.LineNum

方法2：用LEFT JOIN判断关联结果 #

SELECT
    OPOR.DocNum AS PO_Document_Number,
    POR1.U_ContainerNo AS Container_Number,
    CASE
        WHEN PDN1.DocEntry IS NOT NULL THEN 'Shipment Received'
        ELSE 'No Shipment Received'
    END AS Shipment_Status
FROM
    OPOR
INNER JOIN POR1 ON POR1.DocEntry = OPOR.DocEntry
LEFT JOIN PDN1 ON PDN1.BaseEntry = OPOR.DocEntry 
              AND PDN1.BaseLine = POR1.LineNum
LEFT JOIN OPDN ON OPDN.DocEntry = PDN1.DocEntry
GROUP BY
    OPOR.DocNum, POR1.U_ContainerNo, POR1.LineNum, PDN1.DocEntry

关键修正点： #

• 原JOIN条件错误：PDN1.BaseEntry = POR1.DocEntry不成立，PDN1的BaseEntry对应PO表头的DocEntry（OPOR.DocEntry），BaseLine对应PO行的LineNum（POR1.LineNum），需同时满足这两个条件才能关联到正确的GRPO行。
• 添加GROUP BY避免同一个PO行因关联多个GRPO行而重复输出。

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