Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01427: single-row subquery returns more than one row (Oracle 10g)
ORA-01427: single-row subquery returns more than one row [message #336703] Mon, 28 July 2008 14:02 Go to next message
anibeav
Messages: 3
Registered: July 2008
Junior Member
I am receiving this error for a query...I have tried using not in and any throughout the query instead of = but it still is not working. Any thoughts on how to get it to work?

SELECT EMPLOYEE_RECERT_LIST.*, BC_HR_CODE_DESCRIPTIONS.DESC01
FROM EMPLOYEE_RECERT_LIST, BC_HR_CODE_DESCRIPTIONS
WHERE EMPLOYEE_RECERT_LIST.CAT_CD_PAYRL_14 = BC_HR_CODE_DESCRIPTIONS.USER_DEFINED_CD AND
(SELECT EMPLOYEE_RECERT_LIST.ADDR_NUM
FROM EMPLOYEE_RECERT_LIST
WHERE '2008' = SUBSTR(TO_CHAR(EMPLOYEE_RECERT_LIST.UPDT_DT,'MM/DD/YYYY'),-4,4)) NOT IN
(SELECT EMPLOYEE_CERTIFICATION.AN8
FROM EMPLOYEE_CERTIFICATION, BC_HR_CODE_DESCRIPTIONS, EMPLOYEE_RECERT_LIST
WHERE EMPLOYEE_CERTIFICATION.AN8 = EMPLOYEE_RECERT_LIST.ADDR_NUM
AND '2008' = SUBSTR(EMPLOYEE_CERTIFICATION.COURSE, -4,4)
AND BC_HR_CODE_DESCRIPTIONS.PROD_CD = '06'
AND BC_HR_CODE_DESCRIPTIONS.USER_DEFINED_CDS = '14'

ps sorry about the bad structure and if you are wondering the data being returned by the two subqueries is right, just not going together good

Brian
Re: ORA-01427: single-row subquery returns more than one row [message #336705 is a reply to message #336703] Mon, 28 July 2008 14:03 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: ORA-01427: single-row subquery returns more than one row [message #336707 is a reply to message #336705] Mon, 28 July 2008 14:13 Go to previous messageGo to next message
anibeav
Messages: 3
Registered: July 2008
Junior Member
sorry, first time poster...I am running oracle 10g (not sure exact version number) on a Windows 2003 server box. I will look for vers. number and repost with it.

Brian
Re: ORA-01427: single-row subquery returns more than one row [message #336708 is a reply to message #336703] Mon, 28 July 2008 14:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Error? Which error? I don't see any error in your post.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: ORA-01427: single-row subquery returns more than one row [message #336712 is a reply to message #336708] Mon, 28 July 2008 14:32 Go to previous messageGo to next message
anibeav
Messages: 3
Registered: July 2008
Junior Member
the error is in the title... sorry about the bad post, was in a hurry looking for a quick reply.
I had my question answered so i no longer have the problem,
I'll check out all of the guidelines before i post again.

Thanks,

Brian
Re: ORA-01427: single-row subquery returns more than one row [message #336751 is a reply to message #336703] Mon, 28 July 2008 18:25 Go to previous message
Lynn T
Messages: 8
Registered: July 2008
Junior Member
Brian,
Are you saying that the following only returns one record?

Select Employee_Recert_List.Addr_Num
From Employee_Recert_List
Where '2008' = Substr(To_Char(Employee_Recert_List.Updt_Dt,'mm/Dd/Yyyy'),-4,4)
Previous Topic: Restricting a package from other users
Next Topic: Trigger created with compilation error "column not allowed here"
Goto Forum:
  


Current Time: Sat Dec 10 00:58:52 CST 2016

Total time taken to generate the page: 0.04597 seconds