Oracle子查询沿着Case语句导致单行子查询返回多行错误

ctzwtxfj 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(157)

我们有一个在oracle中使用下面的sql的场景，它使用一个Case语句沿着一个子查询，我在尝试执行它时得到以下错误消息。
我得到的错误：
ORA-01427：单行子查询返回多行01427。00000 -“单行子查询返回多行”

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

oracle

来源：https://stackoverflow.com/questions/76968723/oracle-sub-query-along-with-case-statement-in-causing-single-row-subquery-return

2条答案

按热度按时间

izj3ouym1#

RANK解析函数可以返回每个分区中排名第一的多个行，如果有多个行被ORDER BY子句相同地排序。
如果这是原因，那么您可以考虑将RANK更改为ROW_NUMBER，这将在每个分区中仅具有单个行号。
1.如果在RANK ing中有多个分区，那么在每个分区中都会有排在第一位的行，并且CASE表达式期望从子查询中返回一行。
要修复它，您可以聚合：

WHEN (P.SRC_CD IN ('CLIC','PRD5')) THEN (
  SELECT LISTAGG(PARTY_ROLE_NM, ', ') WITHIN GROUP (ORDER BY party_role_nm)
  FROM   (
    SELECT P.AGNT_NO,
           P.AGENCY_NO,
           AP.AGRMNT_ID,
           P.SRC_CD,
           RANK() OVER (                     -- or ROW_NUMBER() OVER (
             PARTITION BY AP.AGRMNT_ID
             ORDER BY PR.PARTY_ROLE_NM DESC
           ) AS 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
)

1.您可能忘记将外部查询与子查询关联起来：

WHEN (P.SRC_CD IN ('CLIC','PRD5')) THEN (
  SELECT LISTAGG(PARTY_ROLE_NM, ', ') WITHIN GROUP (ORDER BY party_role_nm)
  FROM   (
    SELECT P.AGNT_NO,
           P.AGENCY_NO,
           AP.AGRMNT_ID,
           P.SRC_CD,
           RANK() OVER (                     -- or ROW_NUMBER() OVER (
             PARTITION BY AP.AGRMNT_ID
             ORDER BY PR.PARTY_ROLE_NM DESC
           ) AS 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')
  ) x
  WHERE  x.RNK       = 1
  AND    x.agnt_no   = p.agnt_no
  AND    x.agency_no = p.agency_no
  AND    x.agrmnt_id = ap.agrmnt_id
  AND    x.src_id    = p.src_id
)

赞(0）回复(0）举报 2023-10-16

m3eecexj2#

在子查询中

SELECT
                                        RNK,
                                        PARTY_ROLE_NM
                                    FROM
                                        (
                                            SELECT
                                                P.AGNT_NO,
                                                P.AGENCY_NO,
                                                AP.AGRMNT_ID,
                                                P.SRC_CD,
                                                RANK() OVER(PARTITION BY

put count（*）查看是否有重复项

赞(0）回复(0）举报 2023-10-16

我来回答

Oracle子查询沿着Case语句导致单行子查询返回多行错误

2条答案

相关问题

热门标签

最新问答