oracle ora 01427,ORA-01427：单行子查询返回多行 - 如何解决？

我有一个查询，其目的是输出所有角色为"PPC"和"paygroups"这些PPC所属的电子邮件。ORA-01427：单行子查询返回多行 - 如何解决？

有许多薪酬组。

每个paygroup可以有多个PPCs。

每个PPC可能有一个email address 1和/或一个email address 2。

的查询工作正常，当我使用测试paygroup(BOX)，但是当我评论说，行出查询所有paygroups全部归还所有paygroups的PPCs我得到的错误"single-row subquery returns more than one row"。

SELECT

auth.paygroup,

per.NAME,

per.email_address AS "Personal email",

per.email_address2 AS "Business email"

FROM ps_personal_data per

INNER JOIN ps_ts_ee_auth auth

ON auth.emplid=per.emplid

INNER JOIN PS_SMS_SUBSCRB_TBL SUB

ON SUB.SUBSCRIBER_ID = AUTH.PAYGROUP

WHERE 1=1

AND SUB.EFFDT = (SELECT max (SUB2.effdt) FROM PS_SMS_SUBSCRB_TBL SUB2

WHERE SUB.SUBSCRIBER_ID = SUB2.SUBSCRIBER_ID AND SUB.EFF_STATUS = 'A')

AND auth.user_name = (SELECT R1.USER_NAME FROM PS_TS_CONTACT_ROLE R1

WHERE 1=1

AND R1.contact_role = 'PPC'

--AND R1.paygroup = 'BOX' --commenting this line out causes the error. It works fine when line is active

AND R1.action_dt = (SELECT MAX (R2.action_dt)

FROM PS_TS_CONTACT_ROLE R2

WHERE 1=1

AND R1.paygroup = R2.paygroup

AND R1.contact_role =

R2.contact_role))

2016-10-25

Craig B

+1

第1步 - 确定你想要哪一行。 –