如何让子查询返回非空值？Oracle财务表关联查询问题

我在查询中用到三张表：AP_INVOICES_INTERFACE、AP_INVOICE_LINES_INTERFACE，并将PO_HEADERS_ALL作为子查询。其中AP_INVOICE_LINES_INTERFACE只能通过自身的PO_NUMBER与PO_HEADERS_ALL的SEGMENT1字段关联。我的需求是用REQ_BU_ID的值填充REQ_BU_ID2列，填充条件是SEGMENT1等于非空的LN.PO_NUMBER。

当前使用的查询语句如下：

SELECT HDR.INVOICE_ID  , HDR.PO_NUMBER , LN.PO_NUMBER LN_PO_NUMBER

, (SELECT PO2.REQ_BU_ID
    FROM PO_HEADERS_ALL PO2
    WHERE PO2.SEGMENT1 = LN.PO_NUMBER
       AND PO2.REQ_BU_ID IS NOT NULL
       AND LN.PO_NUMBER IS NOT NULL
       --AND HDR.PO_NUMBER IS NOT NULL
       AND rownum = 1    ) REQ_BU_ID2

FROM AP_INVOICES_INTERFACE HDR
INNER JOIN AP_INVOICE_LINES_INTERFACE LN ON LN.INVOICE_ID = HDR.INVOICE_ID
AND HDR.INVOICE_ID = 300000136747640

我原本希望就算LN.PO_NUMBER为空，REQ_BU_ID2也能填充非空值，以为在子查询里加AND LN.PO_NUMBER IS NOT NULL就能实现，但结果还是出现了NULL值。

补充数据示例：

INVOICE_ID        REQ_BU_ID2        PO_NUMBER   LN_PO_NUMBER
300000136747640   300000006290049               K11004499
300000136747640
300000136747640
300000136747640   300000006290049               K11004499

问题根源在于子查询逻辑：当LN.PO_NUMBER为空时，AND LN.PO_NUMBER IS NOT NULL这个条件会直接让子查询返回空结果，导致REQ_BU_ID2为NULL。要实现空值时也填充非空值，你需要明确空值场景下的取值规则，以下两种常见场景的解决方案：

场景1：复用同发票下其他行的有效REQ_BU_ID #

如果希望某行LN.PO_NUMBER为空时，直接使用同一张发票下其他行已获取到的非空REQ_BU_ID，可以用窗口函数实现：

SELECT 
    HDR.INVOICE_ID, 
    HDR.PO_NUMBER, 
    LN.PO_NUMBER LN_PO_NUMBER,
    -- 取同发票下所有有效REQ_BU_ID中的任意非空值，MAX/MIN/ANY_VALUE均可
    MAX(PO2.REQ_BU_ID) OVER (PARTITION BY HDR.INVOICE_ID) REQ_BU_ID2
FROM AP_INVOICES_INTERFACE HDR
INNER JOIN AP_INVOICE_LINES_INTERFACE LN 
    ON LN.INVOICE_ID = HDR.INVOICE_ID
LEFT JOIN PO_HEADERS_ALL PO2
    ON PO2.SEGMENT1 = LN.PO_NUMBER
    AND PO2.REQ_BU_ID IS NOT NULL
WHERE HDR.INVOICE_ID = 300000136747640;

场景2：空值时用表头的PO_NUMBER关联 #

如果希望LN.PO_NUMBER为空时，改用HDR.PO_NUMBER去关联PO_HEADERS_ALL获取REQ_BU_ID，可以用COALESCE函数调整关联条件：

SELECT 
    HDR.INVOICE_ID, 
    HDR.PO_NUMBER, 
    LN.PO_NUMBER LN_PO_NUMBER,
    (SELECT PO2.REQ_BU_ID
     FROM PO_HEADERS_ALL PO2
     WHERE PO2.SEGMENT1 = COALESCE(LN.PO_NUMBER, HDR.PO_NUMBER)
       AND PO2.REQ_BU_ID IS NOT NULL
       AND rownum = 1) REQ_BU_ID2
FROM AP_INVOICES_INTERFACE HDR
INNER JOIN AP_INVOICE_LINES_INTERFACE LN 
    ON LN.INVOICE_ID = HDR.INVOICE_ID
WHERE HDR.INVOICE_ID = 300000136747640;

逻辑说明 #

• 场景1中，窗口函数会将同一张发票下所有行的有效REQ_BU_ID进行聚合，确保每行都能拿到非空值（只要该发票下存在至少一个有效关联）。
• 场景2中，COALESCE函数会优先使用行级的LN.PO_NUMBER，为空时自动切换为表头的HDR.PO_NUMBER，保证子查询能找到匹配的REQ_BU_ID。

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