Oracle Fusion财务报表SQL修改后遇ORA-01427错误求助

Hey there, let's break down why you're hitting that ORA-01427: single-row subquery returns more than one row error after switching tables, and how to fix it.

Why This Happens #

Your original query used AP_INVOICES_ALL, where each INVOICE_ID maps to exactly one invoice record. Even with DISTINCT, this meant the subquery would only ever return one PO_HEADERS_ALL.SEGMENT1 value per invoice—perfect for a CASE statement that expects a single result.

But AP_INVOICE_LINES_ALL is a line-level table: one invoice (INVOICE_ID) can have multiple line items. Even with DISTINCT, if:

• The same invoice links to multiple different purchase orders (multiple unique PO_HEADER_ID values), or
• The query isn't filtered tightly enough to narrow down to one unique SEGMENT1

your subquery will spit out multiple rows, which breaks the CASE statement's requirement for a single value.

Fix Options Based on Your Business Logic #

Option 1: If All Lines for an Invoice Belong to the Same PO

If every line item on an invoice is tied to the same purchase order, use an aggregate function like MAX() or MIN() to force a single result. This is the simplest fix:

SELECT MAX(PHA.SEGMENT1)
FROM AP_INVOICE_LINES_ALL AIA
JOIN PO_HEADERS_ALL PHA 
  ON AIA.PO_HEADER_ID = PHA.PO_HEADER_ID
WHERE AIA.PO_HEADER_ID IS NOT NULL
  AND AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
-- Optional: Add back the invoice number check if needed
-- AND AIA.INVOICE_NUM = XTE.TRANSACTION_NUMBER

Note: I replaced the old comma-style join with explicit JOIN syntax—it's more readable and less error-prone.

Option 2: If an Invoice Can Link to Multiple POs

If your business allows invoices to reference multiple purchase orders, concatenate all related PO numbers into a single string using Oracle's LISTAGG function:

SELECT LISTAGG(PHA.SEGMENT1, ', ') WITHIN GROUP (ORDER BY PHA.SEGMENT1)
FROM AP_INVOICE_LINES_ALL AIA
JOIN PO_HEADERS_ALL PHA 
  ON AIA.PO_HEADER_ID = PHA.PO_HEADER_ID
WHERE AIA.PO_HEADER_ID IS NOT NULL
  AND AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
GROUP BY AIA.INVOICE_ID

This returns all PO numbers associated with the invoice, separated by commas (adjust the delimiter to your needs).

Quick Check: Did You Intentionally Remove the Invoice Number Filter?

You commented out AND AIA.INVOICE_NUM = XTE.TRANSACTION_NUMBER in your modified query. If that filter was necessary to target the correct invoice, add it back—it might help narrow down results to avoid unexpected multiple rows.

Key Takeaway #

The ORA-01427 error always comes down to your subquery returning more than one row when the calling context (like a CASE statement) expects exactly one. By using aggregates or string concatenation, you enforce a single result that works with your CASE logic.

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