Oracle含Case语句子查询报错ORA-01427：单行子查询返回多行

问题描述 #

执行包含Case语句与子查询的Oracle SQL时触发错误：

ORA-01427: single-row subquery returns more than one row（01427. 00000 - "单行子查询返回多行"）

涉及的原SQL语句如下：

SELECT DISTINCT
                P.AGNT_NO,
                P.AGENCY_NO,
                AP.AGRMNT_ID,
                P.SRC_CD,
                PR.PARTY_ROLE_NM,
                APR.AGNT_PARTCPTN_FCTR_NO
            FROM
                EODS.PARTY_EV P
                INNER JOIN EODS.AGRMNT_PARTY_EV AP ON P.PARTY_ID = AP.PARTY_ID
                INNER JOIN EODS.AGRMNT_PARTY_ROLE_EV APR ON AP.AGRMNT_PARTY_ID = APR.AGRMNT_PARTY_ID
                INNER JOIN EODS_REF.REF_PARTY_ROLE_RV PR ON APR.PARTY_ROLE_ID = PR.SRC_ROLE_ID
                AND PR.PARTY_ROLE_NM IN ('SERVICING AGENT','WRITING AGENT')
                INNER JOIN EODS.CVRG C ON AP.AGRMNT_ID = C.AGRMNT_ID
                INNER JOIN EODS_REF.REF_PROD_RV RP ON C.PROD_ID = RP.SRC_PROD_ID
            WHERE
                PR.PARTY_ROLE_NM =
                    CASE
                        WHEN P.SRC_CD = 'PRD4' THEN 'SERVICING AGENT'
                        WHEN (P.SRC_CD = 'ELS' AND RP.PLAN_CD = 'FMYMVAY0') THEN 'SERVICING AGENT'
                        WHEN (P.SRC_CD = 'ELS' AND RP.PLAN_CD = 'INSELDZ0') THEN 'SERVICING AGENT'
                        WHEN (P.SRC_CD = 'ELS' AND RP.PLAN_CD = 'SENSELZ0') THEN 'SERVICING AGENT'
                        WHEN (P.SRC_CD IN ('CLIC','PRD5')) THEN (SELECT
                                PARTY_ROLE_NM
                            FROM
                                (
                                    SELECT
                                        RNK,
                                        PARTY_ROLE_NM
                                    FROM
                                        (
                                            SELECT
                                                P.AGNT_NO,
                                                P.AGENCY_NO,
                                                AP.AGRMNT_ID,
                                                P.SRC_CD,
                                                RANK() OVER(PARTITION BY
                                                    AP.AGRMNT_ID
                                                    ORDER BY
                                                        PR.PARTY_ROLE_NM
                                                    DESC
                                                ) RNK,
                                                PR.PARTY_ROLE_NM,
                                                APR.AGNT_PARTCPTN_FCTR_NO
                                            FROM
                                                EODS.PARTY_EV P
                                                INNER JOIN EODS.AGRMNT_PARTY_EV AP ON P.PARTY_ID = AP.PARTY_ID
                                                INNER JOIN EODS.AGRMNT_PARTY_ROLE_EV APR ON AP.AGRMNT_PARTY_ID = APR.AGRMNT_PARTY_ID
                                                INNER JOIN EODS_REF.REF_PARTY_ROLE_RV PR ON APR.PARTY_ROLE_ID = PR.SRC_ROLE_ID
                                            WHERE
                                                PR.PARTY_ROLE_NM IN ('SERVICING AGENT','WRITING AGENT')
                                        )
                                    WHERE
                                        RNK = 1
                                )
                        )
                        ELSE 'WRITING AGENT'
                    END

错误原因 #

错误出现在Case语句中WHEN (P.SRC_CD IN ('CLIC','PRD5'))对应的子查询分支：

• 子查询使用RANK()窗口函数按AGRMNT_ID分区，按PR.PARTY_ROLE_NM降序排序
• 由于PARTY_ROLE_NM仅包含'SERVICING AGENT'和'WRITING AGENT'两个值，当同一个AGRMNT_ID下同时存在这两个角色时，排序后它们的排名（RNK）会同为1（RANK()会给相同排序值的行分配相同排名）
• 此时WHERE RNK = 1会返回多行数据，而Case语句的分支要求子查询必须返回单行，因此触发ORA-01427错误

解决方案 #

方案1：使用ROW_NUMBER()替代RANK() #

ROW_NUMBER()会为同一分区内的行分配唯一的序号，即使排序字段值相同，也不会出现多个RNK=1的情况，确保子查询仅返回单行：
修改子查询中的窗口函数：

ROW_NUMBER() OVER(PARTITION BY AP.AGRMNT_ID ORDER BY PR.PARTY_ROLE_NM DESC) RNK

方案2：通过聚合函数确保单行返回 #

如果业务上允许取任意一个符合条件的角色值，可以在子查询外层使用MAX()或MIN()聚合函数，强制返回单行：

WHEN (P.SRC_CD IN ('CLIC','PRD5')) THEN (SELECT MAX(PARTY_ROLE_NM) FROM (...))

方案3：重构逻辑，避免Case嵌套子查询 #

将窗口函数逻辑移到主查询的JOIN中，提前计算每个AGRMNT_ID对应的目标角色，再关联到主查询，提升可读性和性能：

WITH AGENT_ROLE AS (
    SELECT
        AP.AGRMNT_ID,
        PARTY_ROLE_NM,
        ROW_NUMBER() OVER(PARTITION BY AP.AGRMNT_ID ORDER BY PR.PARTY_ROLE_NM DESC) RNK
    FROM
        EODS.PARTY_EV P
        INNER JOIN EODS.AGRMNT_PARTY_EV AP ON P.PARTY_ID = AP.PARTY_ID
        INNER JOIN EODS.AGRMNT_PARTY_ROLE_EV APR ON AP.AGRMNT_PARTY_ID = APR.AGRMNT_PARTY_ID
        INNER JOIN EODS_REF.REF_PARTY_ROLE_RV PR ON APR.PARTY_ROLE_ID = PR.SRC_ROLE_ID
    WHERE
        PR.PARTY_ROLE_NM IN ('SERVICING AGENT','WRITING AGENT')
),
TARGET_ROLE AS (
    SELECT AGRMNT_ID, PARTY_ROLE_NM
    FROM AGENT_ROLE
    WHERE RNK = 1
)
SELECT DISTINCT
    P.AGNT_NO,
    P.AGENCY_NO,
    AP.AGRMNT_ID,
    P.SRC_CD,
    PR.PARTY_ROLE_NM,
    APR.AGNT_PARTCPTN_FCTR_NO
FROM
    EODS.PARTY_EV P
    INNER JOIN EODS.AGRMNT_PARTY_EV AP ON P.PARTY_ID = AP.PARTY_ID
    INNER JOIN EODS.AGRMNT_PARTY_ROLE_EV APR ON AP.AGRMNT_PARTY_ID = APR.AGRMNT_PARTY_ID
    INNER JOIN EODS_REF.REF_PARTY_ROLE_RV PR ON APR.PARTY_ROLE_ID = PR.SRC_ROLE_ID
        AND PR.PARTY_ROLE_NM IN ('SERVICING AGENT','WRITING AGENT')
    INNER JOIN EODS.CVRG C ON AP.AGRMNT_ID = C.AGRMNT_ID
    INNER JOIN EODS_REF.REF_PROD_RV RP ON C.PROD_ID = RP.SRC_PROD_ID
    LEFT JOIN TARGET_ROLE TR ON AP.AGRMNT_ID = TR.AGRMNT_ID
WHERE
    PR.PARTY_ROLE_NM =
        CASE
            WHEN P.SRC_CD = 'PRD4' THEN 'SERVICING AGENT'
            WHEN (P.SRC_CD = 'ELS' AND RP.PLAN_CD IN ('FMYMVAY0','INSELDZ0','SENSELZ0')) THEN 'SERVICING AGENT'
            WHEN (P.SRC_CD IN ('CLIC','PRD5')) THEN TR.PARTY_ROLE_NM
            ELSE 'WRITING AGENT'
        END

补充优化 #

原Case语句中多个ELS的PLAN_CD判断可以合并为IN条件，简化代码，如上述重构后的SQL所示。

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