Want subquery to return non-Null values-CodePudding

I have three tables I am using in a query; AP_INVOICES_INTERFACE , AP_INVOICE_LINES_INTERFACE , and PO_HEADERS_ALL as a subquery.

The AP_INVOICE_LINES_INTERFACE table is joined to PO_HEADERS_ALL only on the PO_NUMBER from AP_INVOICE_LINES_INTERFACE to the SEGMENT1 in PO_HEADERS_ALL. I want to populate the column 'REQ_BU_ID2' with the REQ_BU_ID value based on SEGMENT1 equaling LN.PO_NUMBER where it is not NULL.

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

I want to populate the REQ_BU_ID2 (non-null) value for rows even when the LN.PO_NUMBER is NULL so I thought by using the AND LN.PO_NUMBER IS NOT NULL condition in the Subquery would then only return the non-null value, however as you can see it is still returning Null as well in the results:

Current Results from above query:

Desired Results:

Edit:

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

CodePudding user response：

The below uses a CTE to give all INVOICE_IDs the same LN_PO_NUMBER.

If they can legitimately ever have different "non-NULL" PO_NUMBERs, this won't work.

Then your sub-query has been adapted to use ORDER BY REQ_BU_ID DESC FETCH NEXT 1 ROWS ONLY in order to find exactly one REQ_BU_ID in a deterministic way, preferring non-NULL values.

Again, if any PO_NUMBER can legitimately relate to more than 1 non-NULL REQ_BU_ID, this won't work.

Also, if ZERO REQ_BU_ID are found, it still returns NULL.

WITH
  PO_LOOKUP
AS
(
  SELECT
    HDR.INVOICE_ID,
    HDR.PO_NUMBER,
    MAX(LN.PO_NUMBER) OVER (PARTITION BY HDR.INVOICE_ID)   AS LN_PO_NUMBER
  FROM
    AP_INVOICES_INTERFACE        HDR
  INNER JOIN
    AP_INVOICE_LINES_INTERFACE   LN
      ON LN.INVOICE_ID = HDR.INVOICE_ID
)
SELECT
  PO_LOOKUP.*,
  (
    SELECT REQ_BU_ID
      FROM PO_HEADERS_ALL
     WHERE SEGMENT1 = PO_LOOKUP.LN_PO_NUMBER
  ORDER BY REQ_BU_ID ASC
     FETCH NEXT 1 ROWS ONLY
  )
    AS REQ_BU_ID
FROM
  PO_LOOKUP
WHERE
  INVOICE_ID = 300000136747640

Demo : https://dbfiddle.uk/N9fb6W9I

EDIT: Alternatively...

SELECT
  HDR.INVOICE_ID,
  HDR.PO_NUMBER,
  LN.MAX_PO_NUMBER,
  PO.MAX_REQ_BU_ID
FROM
  AP_INVOICES_INTERFACE        HDR
INNER JOIN
(
  SELECT
    AP_INVOICE_LINES_INTERFACE.*,
    MAX(PO_NUMBER) OVER (PARTITION BY INVOICE_ID)  AS MAX_PO_NUMBER
  FROM
    AP_INVOICE_LINES_INTERFACE
)
  LN
    ON LN.INVOICE_ID = HDR.INVOICE_ID
INNER JOIN
(
  SELECT
    SEGMENT1,
    MAX(REQ_BU_ID)   AS MAX_REQ_BU_ID
  FROM
    PO_HEADERS_ALL
  GROUP BY
    SEGMENT1
)
  PO
    ON PO.SEGMENT1 = LN.MAX_PO_NUMBER
WHERE
  HDR.INVOICE_ID = 300000136747640