Hi all,
I have a cursor (not written by me) that I am debugging. When I run this cursor at SQL prompt/TOAD, I get this message "ORA-01427: single-row subquery returns more than one row"
I realize that the sub query must be returning more than 1 row. However I am unable to debug it because it is a correlated subquery.
Question is - how do I go about and debug this query. I did replace '=' with IN operator near the MAX function of the subquery but to no avail. It throws the same error.
Can anyone shed some light on this as I am unable to debug this.
--cursor c_gl_feed_file_2
--- is
select System_ID
||File_ID
-- ||Ref_Num Ref_num,
Doc_Text_Seq_Num1,
Text1,
Doc_Text_Seq_Num2,
text2,
Doc_Text_Seq_Num3,
text3,
Doc_Text_Seq_Num4,
text4,
Doc_Text_Seq_Num5,
text5
from ( Select 'ARRUGAJE' System_ID,
to_char(SYSDATE,'YYYYMMDD') File_ID,
-- v_ORDER_TRXN_ID Ref_Num ,
'1' Doc_Text_Seq_Num1,
'For additional information please contact:' Text1,
'2' Doc_Text_Seq_Num2,
GZBFCTL_EMAIL_ADDRESS text2,
'3' Doc_Text_Seq_Num3,
SPRIDEN_FIRST_NAME || ' '|| SPRIDEN_LAST_NAME text3,
'4' Doc_Text_Seq_Num4,
SPRADDR_STREET_LINE1 text4,
'5' Doc_Text_Seq_Num5,
--
(select SPRTELE_PHONE_AREA || ' '||
SPRTELE_PHONE_NUMBER || ' ' ||
SPRTELE_PHONE_EXT
from SPVTELE_NO_PRIV
where SPRTELE_PIDM = GZBFCTL.GZBFCTL_PIDM
and SPRTELE_TELE_CODE = 'MPB'
and SPRTELE_ATYP_CODE = 'CA'
) text5
from GZBFCTL,SPRIDEN,SPVADDR_NO_PRIV t
where GZBFCTL.GZBFCTL_MCGILL_SYSTEM_ID = 'ARRUGAJE'
and GZBFCTL.GZBFCTL_PIDM = SPRIDEN.SPRIDEN_PIDM
and SPRADDR_PIDM = GZBFCTL.GZBFCTL_PIDM
and SPRADDR_ATYP_CODE = 'CA'
and SPRADDR_ACTIVITY_DATE = (select max(SPRADDR_ACTIVITY_DATE)
from SPVADDR_NO_PRIV
where SPRADDR_PIDM = t.SPRADDR_PIDM
and SPRADDR_ATYP_CODE = 'CA'
)
and rownum=1
);