Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Janusz Jasinski
    Amending SQL slightly brings me: ORA-01427: single-row...
    Topic posted October 30, 2019 by Janusz JasinskiSilver Crown: 22,500+ Points, tagged BI Publisher, Public Sector, Reports 
    36 Views, 3 Comments
    Title:
    Amending SQL slightly brings me: ORA-01427: single-row subquery returns more than one row
    Summary:
    ORA-01427
    Content:

    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

    Comment

     

    • Mark daynes

      Distinct will remove duplicates but if a query returns mutliple different values then you'll get multiple values.  If i'm following what your doing correctly (and there's no guarantees there as I can't see what the alias XTE refers to) then if you were joining to a table like AP_INVOICES and have changed it to a lower level table such as AP_INVOICE_LINES (where an invoice can have multiple lines) then is that not where the problem is occuring?

    • Sudhakara Rao Kovuru

      Please share the complete SQL query then will be able to help you to fix it

    • Michael Cook
      Why not putting max() around the column name of the sub query