Home » SQL & PL/SQL » SQL & PL/SQL » Help with single-row subquery returns more than one row.
Help with single-row subquery returns more than one row. [message #357676] Thu, 06 November 2008 04:09 Go to next message
ch_praveen2007
Messages: 1
Registered: November 2008
Junior Member
Hi All,

I'm using this query to find the records which have A.ID column as NULL and insert into the ACTIVITY table with the criteria as ID, NUM, STAT, ACTIVITYID and BU.


INSERT INTO ACTIVITY
(ID, NUM, STAT, ACTIVITYID, BU)
SELECT ROWNUM,
6112008,
'UNDER_PROGRESS',
ACTIVITYID,
(SELECT NAME
FROM BU
WHERE ID IN
(SELECT ID
FROM POSTN
WHERE EMPID IN (SELECT ROW_ID FROM USER WHERE ROW_ID = EMP_ID)))
FROM ACT A, ACT_EMP B
WHERE A.ID = B.ACTIVITYID
AND A.ID IS NULL

This query returns an error showing

ORA - 01427 : single-row subquery returns more than one row.

Please help me out with this issue.
Thanks In Advance.
Praveen.
Re: Help with single-row subquery returns more than one row. [message #357681 is a reply to message #357676] Thu, 06 November 2008 04:20 Go to previous messageGo to next message
deepshikhahcl
Messages: 14
Registered: October 2008
Junior Member
I need more clarification like the relation between the table ACT and ACT_EMP and postEN.
Can you send the create table script of all three so that i can send you the solution
Re: Help with single-row subquery returns more than one row. [message #357694 is a reply to message #357676] Thu, 06 November 2008 05:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: Help with single-row subquery returns more than one row. [message #357699 is a reply to message #357676] Thu, 06 November 2008 05:05 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Quote:
ORA - 01427 : single-row subquery returns more than one row


make sure that subquery return just One record by using proper WHERE condition .

Smile
Rajuvan.
Re: Help with single-row subquery returns more than one row. [message #357833 is a reply to message #357676] Thu, 06 November 2008 21:09 Go to previous messageGo to next message
fairgame
Messages: 29
Registered: October 2008
Junior Member
one of your inner query is returning more than one row, run your inner query separately and check the output.
Re: Help with single-row subquery returns more than one row. [message #358127 is a reply to message #357676] Sat, 08 November 2008 20:04 Go to previous messageGo to next message
shobanasree
Messages: 9
Registered: November 2008
Location: Bangalore
Junior Member
Hi Praveen,

Use cursor to select the below statement.

SELECT ROWNUM,
6112008,
'UNDER_PROGRESS',
ACTIVITYID,
(SELECT NAME
FROM BU
WHERE ID IN
(SELECT ID
FROM POSTN
WHERE EMPID IN (SELECT ROW_ID FROM USER WHERE ROW_ID = EMP_ID)))
FROM ACT A, ACT_EMP B
WHERE A.ID = B.ACTIVITYID
AND A.ID IS NULL


Inside the for loop or Open cursor loop write the insert statement.
Now your query should work fine.
Re: Help with single-row subquery returns more than one row. [message #358135 is a reply to message #358127] Sun, 09 November 2008 00:20 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@shobanasree,

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: sql script issue.. Please help
Next Topic: How to use a clob column in group by clause
Goto Forum:
  


Current Time: Wed Dec 07 16:14:40 CST 2016

Total time taken to generate the page: 0.17053 seconds