如何在子查询中获取多列？ORA-00913: 值过多错误求助

嘿，我来帮你搞定这个ORA-00913错误！这个问题本质是你在SELECT列表里用的子查询返回了多个列（比如地址的ADDR1、ADDR2等一共5个字段），但Oracle不允许在SELECT的单个字段位置接收多列值，所以才报“too many values”。下面给你几个可行的解决思路：

把获取地址的逻辑改成JOIN关联，这样可以直接把多个地址字段放到SELECT列表里，既避免子查询返回多列的问题，性能也更优。另外要注意给表加别名，避免像BANK_ID = BANK_ID这种字段歧义（原来的写法会导致全表扫描，因为Oracle会认为是同一张表的字段对比）。

示例代码：

SELECT 
    dr.ID, 
    dr.CASENO, 
    CASE WHEN dr.PMT_METHOD = 'B' THEN bm.ROUTE_TRANS || bm.CHK_DIGIT END AS ROUTING_NUMBER,
    -- 优先取有inv_id='ADMIN'的地址，没有则取备选地址
    COALESCE(cr_admin.ADDR1, cr_backup.CA_ADDR1) AS ADDR1,
    COALESCE(cr_admin.ADDR2, cr_backup.CA_ADDR2) AS ADDR2,
    COALESCE(cr_admin.CITY, cr_backup.CA_CITY) AS CITY,
    COALESCE(cr_admin.ADD_STATE, cr_backup.CA_STATE) AS ADD_STATE,
    COALESCE(cr_admin.ZIP, cr_backup.CA_ZIP) AS ZIP
FROM DUMMY_REQUESTS dr
-- 关联银行表获取路由号（注意主表和子表的BANK_ID关联）
LEFT JOIN BANK_MASTER bm ON dr.BANK_ID = bm.BANK_ID
-- 关联有inv_id='ADMIN'的合法地址
LEFT JOIN (
    SELECT cl.CLTID, ca.ADDR1, ca.ADDR2, ca.CITY, ca.ADD_STATE, ca.ZIP
    FROM DB.clientref cl
    JOIN DB.client_address ca ON cl.addressid = ca.address_id
    WHERE ca.addresstype = 'L' AND ca.disabled = 'N' AND cl.inv_id = 'ADMIN'
) cr_admin ON dr.CLIENTID = cr_admin.CLTID
-- 关联没有ADMIN地址时的备选地址（只取一行）
LEFT JOIN (
    SELECT cl.CLTID, cl.CA_ADDR1, cl.CA_ADDR2, cl.CA_CITY, cl.CA_STATE, cl.CA_ZIP
    FROM DB.clientref cl
    JOIN DB.client_address ca ON cl.addressid = ca.address_id
    WHERE ca.addresstype = 'L' AND ca.disabled = 'N'
    -- 用EXISTS替代COUNT(*)判断，性能更好
    AND NOT EXISTS (
        SELECT 1 
        FROM DB.clientref cl2
        JOIN DB.client_address ca2 ON cl2.addressid = ca2.address_id
        WHERE ca2.addresstype = 'L' AND ca2.disabled = 'N' 
          AND cl2.inv_id = 'ADMIN' AND cl2.CLTID = cl.CLTID
    )
    AND ROWNUM = 1
) cr_backup ON dr.CLIENTID = cr_backup.CLTID
WHERE dr.ID = '1234';

如果确实需要用子查询返回地址信息，可以把多个字段用字符串拼接成单个值，这样子查询就只返回单列了：

示例代码：

SELECT 
    ID, 
    CASENO, 
    -- 注意这里要指定主表的BANK_ID，避免歧义
    CASE WHEN PMT_METHOD = 'B' THEN (
        SELECT ROUTE_TRANS || CHK_DIGIT 
        FROM BANK_MASTER 
        WHERE BANK_ID = DUMMY_REQUESTS.BANK_ID 
    ) END AS ROUTING_NUMBER,
    -- 把地址字段拼接成一个完整字符串
    (
        SELECT ADDR1 || ', ' || ADDR2 || ', ' || CITY || ', ' || ADD_STATE || ' ' || ZIP 
        FROM DB.clientref, DB.client_address 
        WHERE addressid = address_id AND addresstype = 'L' AND disabled = 'N' 
          AND inv_id = 'ADMIN' AND CLTID = CLIENTID 
        UNION ALL 
        SELECT CA_ADDR1 || ', ' || CA_ADDR2 || ', ' || CA_CITY || ', ' || CA_STATE || ' ' || CA_ZIP 
        FROM DB.clientref, DB.client_address 
        WHERE addressid = address_id AND addresstype = 'L' AND disabled = 'N' 
          AND CLTID = CLIENTID 
          AND NOT EXISTS (
              SELECT 1 
              FROM DB.clientref, DB.client_address 
              WHERE addressid = address_id AND addresstype = 'L' AND disabled = 'N' 
                AND inv_id = 'ADMIN' AND CLTID = CLIENTID 
          ) 
        AND ROWNUM = 1
    ) AS FULL_ADDRESS
FROM DUMMY_REQUESTS 
WHERE ID = '1234';

如果需要单独获取每个地址字段，可以把原来的多列子查询拆分成多个单行单列的子查询，但这种方法会多次执行子查询，性能较差，仅适用于特殊场景：

示例代码：

SELECT 
    ID, 
    CASENO, 
    CASE WHEN PMT_METHOD = 'B' THEN (
        SELECT ROUTE_TRANS || CHK_DIGIT 
        FROM BANK_MASTER 
        WHERE BANK_ID = DUMMY_REQUESTS.BANK_ID 
    ) END AS ROUTING_NUMBER,
    -- 每个地址字段对应一个子查询
    (SELECT ADDR1 FROM DB.clientref, DB.client_address WHERE addressid = address_id AND addresstype = 'L' AND disabled = 'N' AND inv_id = 'ADMIN' AND CLTID = CLIENTID) AS ADDR1,
    (SELECT ADDR2 FROM DB.clientref, DB.client_address WHERE addressid = address_id AND addresstype = 'L' AND disabled = 'N' AND inv_id = 'ADMIN' AND CLTID = CLIENTID) AS ADDR2,
    -- 用COALESCE处理没有ADMIN地址的情况
    COALESCE(
        (SELECT CITY FROM DB.clientref, DB.client_address WHERE addressid = address_id AND addresstype = 'L' AND disabled = 'N' AND inv_id = 'ADMIN' AND CLTID = CLIENTID),
        (SELECT CA_CITY FROM DB.clientref, DB.client_address WHERE addressid = address_id AND addresstype = 'L' AND disabled = 'N' AND CLTID = CLIENTID AND ROWNUM = 1)
    ) AS CITY
FROM DUMMY_REQUESTS 
WHERE ID = '1234';

• 表关联时务必加别名，避免字段名冲突（比如DUMMY_REQUESTS.BANK_ID = BANK_MASTER.BANK_ID才是正确的关联写法）；
• UNION ALL的两个分支要保证列数、数据类型完全一致，否则会触发其他错误；
• 判断记录是否存在时，用EXISTS替代COUNT(*)，性能会提升很多。

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