Amending SQL slightly brings me: ORA-01427: single-row subquery returns more than one row

Question

I am using Oracle Fusion, the finance/payables reporting side of things

I have the following code:

SELECT DISTINCT PHA.SEGMENT1 
FROM 
    AP_INVOICES_ALL AIA, -- https://docs.oracle.com/en/cloud/saas/financials/19a/oedmf/AP_INVOICES_ALL-tbl.html
    PO_HEADERS_ALL PHA -- https://docs.oracle.com/applications/farel12/procurementop_gs/OEDMP/PO_HEADERS_ALL_tbl.htm
WHERE
    AIA.PO_HEADER_ID = PHA.PO_HEADER_ID 
    AND AIA.PO_HEADER_ID IS NOT NULL 
    AND AIA.INVOICE_NUM = XTE.TRANSACTION_NUMBER 
    AND AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1

This "works" in that it returns no errors. However it's looking at the wrong table so wrong data is being brought back. No biggie, we have identified which table it is.

It's used in a wider context as below to define a column value

( 
        CASE
          WHEN
            GJH.JE_SOURCE IN 
            (
              'Purchasing', 'Payables'
            )
          THEN
( 
            SELECT DISTINCT
              PHA.SEGMENT1 
            FROM
              AP_INVOICES_ALL AIA, PO_HEADERS_ALL PHA 
            WHERE
              AIA.PO_HEADER_ID = PHA.PO_HEADER_ID 
              AND AIA.PO_HEADER_ID IS NOT NULL 
              AND AIA.INVOICE_NUM = XTE.TRANSACTION_NUMBER 
              AND AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1

              ) 
            WHEN
              GJH.JE_SOURCE IN 
              (
                'Receipt Accounting'
              )
            THEN
( 
              SELECT
                CPOD.PO_NUMBER 
              FROM
                --cmr_transactions ct
                CMR_PURCHASE_ORDER_DTLS CPOD , CMR_RCV_EVENTS CRE , POZ_SUPPLIERS_V PS 
              WHERE
                CRE.CMR_PO_DISTRIBUTION_ID = CPOD.CMR_PO_DISTRIBUTION_ID 
                AND CPOD.VENDOR_ID = PS.VENDOR_ID 
                AND CPOD.ACTIVE_FLAG = 'Y' 
                AND CRE.ACCOUNTING_EVENT_ID = XTE.SOURCE_ID_INT_1 ) 
              ELSE
                NULL 
        END
) AS PURCHORDERNUM,

However I need to amend the SQL to be the following:

SELECT DISTINCT PHA.SEGMENT1 
FROM
    AP_INVOICE_LINES_ALL AIA, -- https://docs.oracle.com/en/cloud/saas/financials/19a/oedmf/AP_INVOICE_LINES_ALL-tbl.html
    PO_HEADERS_ALL PHA 
WHERE
    AIA.PO_HEADER_ID = PHA.PO_HEADER_ID 
    AND AIA.PO_HEADER_ID IS NOT NULL 
    --AND AIA.INVOICE_NUM = XTE.TRANSACTION_NUMBER 
    AND AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1

This now brings me back ORA-01427: single-row subquery returns more than one row but I can not see why. I am merely changing the table it is looking at and using DISTINCT

Answer 1

The table has different data in it, and what the error message is telling you is that the filters you are using AIA.PO_HEADER_ID IS NOT NULL AND AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1 are returning more than one row for at least one value of XTE.SOURCE_ID_INT_1

You probably need to narrow it down a bit.

Try this - on its own - just to see what sort of values your subquery is returning for various values of XTE.SOURCE_ID_INT_1

SELECT DISTINCT AIA.INVOICE_ID AS MATCHES_SOURCE_ID_INT_1, PHA.SEGMENT1 
FROM
    AP_INVOICE_LINES_ALL AIA, -- https://docs.oracle.com/en/cloud/saas/financials/19a/oedmf/AP_INVOICE_LINES_ALL-tbl.html
    PO_HEADERS_ALL PHA 
WHERE
    AIA.PO_HEADER_ID = PHA.PO_HEADER_ID 
    AND AIA.PO_HEADER_ID IS NOT NULL 
    --AND AIA.INVOICE_NUM = XTE.TRANSACTION_NUMBER 
    AND AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1

Answer 2

Using DISTINCT does not mean you will only get one record back. It means you will get one of each distinct values in your result set. That means if 5 records are returned:

value1
value2
value2
value1
value3

Using DISTINCT you will get a result set that looks like this:

value1
value2
value3

You need to ensure that you have enough criteria in your WHERE or ON clauses to ensure a single value return.