Oracle SQL中FROM子句用NVL嵌套查询遇ORA-00913错误求助

错误原因 #

ORA-00913: 数值过多的根本原因不是NVL的第二个参数不能是查询，而是NVL函数要求两个参数都必须是单个标量值，但你的两个子查询都返回了多列、多行的结果集，这完全超出了NVL的处理能力——NVL只能处理单值的空值替换，无法处理集合之间的替换。

替代方案 #

你想要的逻辑是：优先使用第一个子查询的结果，当第一个子查询无数据时，再用第二个子查询的结果关联主表。以下是两种可行的实现方式：

方案1：WITH子句 + LEFT JOIN + COALESCE #

先将两个子查询定义为公共表表达式，分别LEFT JOIN到主表，最后用COALESCE逐字段取第一个非空值：

WITH cst_data AS (
    SELECT A.INVENTORY_ITEM_ID,
           C.SUBINVENTORY_CODE,
           TO_CHAR(MAX(B.COST_DATE)) COST_DATE,
           TO_CHAR(B.TRANSACTION_COST,'FM99990.90') TRANSACTION_COST
    FROM CST_ONHAND_V A
         JOIN CST_ITEM_COST_HISTORY_V B 
             ON A.REC_TRXN_ID = B.TRANSACTION_ID
             AND A.COST_ORG_ID = B.COST_ORG_ID
             AND A.COST_BOOK_ID = B.COST_BOOK_ID
             AND A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
         JOIN CST_TXN_LAYER_DTLS_V C 
             ON A.REC_TRXN_ID = C.REC_TRXN_ID
    WHERE A.INVENTORY_ITEM_ID = :p_inv_num
    GROUP BY A.INVENTORY_ITEM_ID,
             C.SUBINVENTORY_CODE,
             TO_CHAR(B.TRANSACTION_COST,'FM99990.90')
),
po_data AS (
    SELECT INV.INVENTORY_ITEM_ID,
           SEC.SECONDARY_INVENTORY_NAME SUBINVENTORY_CODE,
           '' COST_DATE,
           NVL(TO_CHAR((LN.UNIT_PRICE / UOM.CONVERSION_RATE),'FM99990.90'),LN.UNIT_PRICE) TRANSACTION_COST
    FROM INV_ITEM_SUB_INVENTORIES INV
         JOIN inv_secondary_inventories SEC 
             ON SEC.SECONDARY_INVENTORY_NAME = INV.SECONDARY_INVENTORY
             AND SEC.organization_id = INV.organization_id
         JOIN PO_LINES_ALL LN 
             ON LN.ITEM_ID = INV.INVENTORY_ITEM_ID
         JOIN PO_HEADERS_ALL HDR 
             ON HDR.PO_HEADER_ID = LN.PO_HEADER_ID
         LEFT JOIN INV_UOM_CONVERSIONS UOM 
             ON UOM.INVENTORY_ITEM_ID = INV.INVENTORY_ITEM_ID
             AND UOM.UOM_CODE = LN.UOM_CODE
    WHERE SEC.ASSET_INVENTORY = 2
      AND HDR.type_lookup_code = 'BLANKET'
      AND INV.INVENTORY_ITEM_ID = :p_inv_num
      AND SEC.attribute1 = 'Y'
)
SELECT IOQD.*,
       ESI.*,
       COALESCE(cst.INVENTORY_ITEM_ID, po.INVENTORY_ITEM_ID) AS INVENTORY_ITEM_ID,
       COALESCE(cst.SUBINVENTORY_CODE, po.SUBINVENTORY_CODE) AS SUBINVENTORY_CODE,
       COALESCE(cst.COST_DATE, po.COST_DATE) AS COST_DATE,
       COALESCE(cst.TRANSACTION_COST, po.TRANSACTION_COST) AS TRANSACTION_COST
FROM inv_onhand_quantities_detail IOQD
     JOIN egp_system_items ESI 
         ON IOQD.inventory_item_id = ESI.inventory_item_id
         AND IOQD.organization_id = ESI.organization_id
     LEFT JOIN cst_data cst 
         ON cst.SUBINVENTORY_CODE = IOQD.SUBINVENTORY_CODE
         AND cst.INVENTORY_ITEM_ID = IOQD.INVENTORY_ITEM_ID
     LEFT JOIN po_data po 
         ON po.SUBINVENTORY_CODE = IOQD.SUBINVENTORY_CODE
         AND po.INVENTORY_ITEM_ID = IOQD.INVENTORY_ITEM_ID
WHERE ESI.inventory_item_id = :p_inv_num;

方案2：UNION ALL + 优先级过滤 #

如果两个子查询的结果不会重叠，或者需要严格优先使用第一个子查询的结果，可以用UNION ALL合并后通过优先级标记筛选：

WITH combined_data AS (
    SELECT 1 AS priority,
           A.INVENTORY_ITEM_ID,
           C.SUBINVENTORY_CODE,
           TO_CHAR(MAX(B.COST_DATE)) COST_DATE,
           TO_CHAR(B.TRANSACTION_COST,'FM99990.90') TRANSACTION_COST
    FROM CST_ONHAND_V A
         JOIN CST_ITEM_COST_HISTORY_V B 
             ON A.REC_TRXN_ID = B.TRANSACTION_ID
             AND A.COST_ORG_ID = B.COST_ORG_ID
             AND A.COST_BOOK_ID = B.COST_BOOK_ID
             AND A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
         JOIN CST_TXN_LAYER_DTLS_V C 
             ON A.REC_TRXN_ID = C.REC_TRXN_ID
    WHERE A.INVENTORY_ITEM_ID = :p_inv_num
    GROUP BY A.INVENTORY_ITEM_ID,
             C.SUBINVENTORY_CODE,
             TO_CHAR(B.TRANSACTION_COST,'FM99990.90')
    UNION ALL
    SELECT 2 AS priority,
           INV.INVENTORY_ITEM_ID,
           SEC.SECONDARY_INVENTORY_NAME SUBINVENTORY_CODE,
           '' COST_DATE,
           NVL(TO_CHAR((LN.UNIT_PRICE / UOM.CONVERSION_RATE),'FM99990.90'),LN.UNIT_PRICE) TRANSACTION_COST
    FROM INV_ITEM_SUB_INVENTORIES INV
         JOIN inv_secondary_inventories SEC 
             ON SEC.SECONDARY_INVENTORY_NAME = INV.SECONDARY_INVENTORY
             AND SEC.organization_id = INV.organization_id
         JOIN PO_LINES_ALL LN 
             ON LN.ITEM_ID = INV.INVENTORY_ITEM_ID
         JOIN PO_HEADERS_ALL HDR 
             ON HDR.PO_HEADER_ID = LN.PO_HEADER_ID
         LEFT JOIN INV_UOM_CONVERSIONS UOM 
             ON UOM.INVENTORY_ITEM_ID = INV.INVENTORY_ITEM_ID
             AND UOM.UOM_CODE = LN.UOM_CODE
    WHERE SEC.ASSET_INVENTORY = 2
      AND HDR.type_lookup_code = 'BLANKET'
      AND INV.INVENTORY_ITEM_ID = :p_inv_num
      AND SEC.attribute1 = 'Y'
),
ranked_data AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY INVENTORY_ITEM_ID, SUBINVENTORY_CODE ORDER BY priority) AS rn
    FROM combined_data
)
SELECT IOQD.*,
       ESI.*,
       rd.INVENTORY_ITEM_ID,
       rd.SUBINVENTORY_CODE,
       rd.COST_DATE,
       rd.TRANSACTION_COST
FROM inv_onhand_quantities_detail IOQD
     JOIN egp_system_items ESI 
         ON IOQD.inventory_item_id = ESI.inventory_item_id
         AND IOQD.organization_id = ESI.organization_id
     LEFT JOIN ranked_data rd 
         ON rd.SUBINVENTORY_CODE = IOQD.SUBINVENTORY_CODE
         AND rd.INVENTORY_ITEM_ID = IOQD.INVENTORY_ITEM_ID
         AND rd.rn = 1
WHERE ESI.inventory_item_id = :p_inv_num;

说明 #

• 方案1适合两个子查询可能有部分重叠的场景，通过COALESCE灵活取各字段的有效值；
• 方案2适合需要严格优先使用第一个子查询结果的场景，即使两个子查询有相同的(INVENTORY_ITEM_ID, SUBINVENTORY_CODE)组合，也只会保留优先级最高的记录。

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