OraFAQ Forum: SQL & PL/SQL » Single-row subquery Issue

Home » SQL & PL/SQL » SQL & PL/SQL » Single-row subquery Issue (10G)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Single-row subquery Issue [message #577139]

Tue, 12 February 2013 14:31

gblackiv
Messages: 15
Registered: August 2012
Location: Springfield VA

Junior Member

I have two update queries in the same Proc. One Seems to run just fine, the other I am getting this error:

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

The working Updates' structure is the same as the erroneous one.
This works:

    
UPDATE P0525_STOREROOM_HOLDER H
    SET H.STRATIFICATION_ID = (SELECT X.STRATIFICATION_ID
                               FROM EMISTRATIFICATION_XS X
                               WHERE H.TOA = X.TOA
                               AND H.STOREROOM = X.STOREROOM
                               AND H.NSN = X.NSN
                               AND X.ASSEMBLY = 'NO REQUIREMENT' );

This one gives me a single-row error:

   UPDATE P0525_STOREROOM_HOLDER H
    SET H.STRATIFICATION_ID = (SELECT X.STRATIFICATION_ID
                               FROM EMISTRATIFICATION_XS X
                               WHERE H.TOA = X.TOA
                               AND H.STOREROOM = X.STOREROOM
                               AND H.NSN = X.NSN
                               AND X.ASSEMBLY = 'ABOVE ALLOWANCE'
                               AND H.NSN_QUANTITY > 0);

Can someone explain why this might be happening? I have run a check on the data and there doesn't appear to be any duplicate values in the second update... Both Updates are supposed to be updating record sets not a single row (i.e. the stratification_id where the criteria matches... so I don't understand why one would work and the other would not.

Any insight is greatly appreciated,
-Gary

Report message to a moderator

Re: Single-row subquery Issue [message #577141 is a reply to message #577139]

Tue, 12 February 2013 14:37

BlackSwan
Messages: 20242
Registered: January 2009

Senior Member

SELECT COUNT(*)
FROM EMISTRATIFICATION_XS X
WHERE H.TOA = X.TOA
AND H.STOREROOM = X.STOREROOM
AND H.NSN = X.NSN
AND X.ASSEMBLY = 'ABOVE ALLOWANCE'
AND H.NSN_QUANTITY > 0;

what does SQL produce?
Any value greater than will throw the error.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Single-row subquery Issue [message #577149 is a reply to message #577139]

Tue, 12 February 2013 15:23

gblackiv
Messages: 15
Registered: August 2012
Location: Springfield VA

Junior Member

This code doesn't work cuz it's missing the table H (P0525_STOREROOM_HOLDER)

SELECT COUNT(*)
FROM EMISTRATIFICATION_XS X
WHERE H.TOA = X.TOA
AND H.STOREROOM = X.STOREROOM
AND H.NSN = X.NSN
AND X.ASSEMBLY = 'ABOVE ALLOWANCE'
AND H.NSN_QUANTITY > 0;

Any value greater than...?

Report message to a moderator

Re: Single-row subquery Issue [message #577150 is a reply to message #577149]

Tue, 12 February 2013 15:28

Littlefoot
Messages: 17245
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Gee, so add the "H" table to the FROM clause ...

Anyway, expected result is larger than 1. What you should do is to add additional condition(s) to the WHERE clause which will further restrict result set and make sure that at most 1 record is returned. Otherwise, Oracle just doesn't know which value of 2, 3, ... , many X.STRATIFICATION_ID to put into H.STRATIFICATION_ID. You know, the ./fa/2883/0/

principle: there can be only one sitting there at the moment.

Report message to a moderator

Re: Single-row subquery Issue [message #577151 is a reply to message #577149]

Tue, 12 February 2013 15:32

gblackiv
Messages: 15
Registered: August 2012
Location: Springfield VA

Junior Member

Ah I see what you're getting at... and I found the issue (I think) there is a duplicate value I didn't see. I ran the select count with the other table linked and got a count of 29 when there were only 27 records.

Thanks so much!

Report message to a moderator

Previous Topic:	NVL2 problem when using forms
Next Topic:	logic to check record exist in tabl

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 18 19:22:29 CDT 2013

Total time taken to generate the page: 0.22870 seconds