Craig B Craig B - 1 month ago 6
SQL Question

ORA-01427:single-row subquery returns more than one row - how to fix?

I have a query with the purpose of outputting all emails that have a role of

"PPC"
and the
"paygroups"
those PPCs belong to.


  • There are many paygroups.

  • Each
    paygroup
    can have multiple
    PPCs
    .

  • Each
    PPC
    may have an
    email address 1
    and/or an
    email address 2
    .



The query works fine when I use a test
paygroup(BOX)
, but when I comment that line out to query for all
paygroups
to return all the
PPCs
of all the
paygroups
I get the error
"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))

Answer
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 in (SELECT R1.USER_NAME FROM PS_TS_CONTACT_ROLE R1 -- Change = to in
                      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))