Home » SQL & PL/SQL » SQL & PL/SQL » Single-row subquery Issue (10G)
Single-row subquery Issue [message #577139] Tue, 12 February 2013 14:31 Go to next message
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
Re: Single-row subquery Issue [message #577141 is a reply to message #577139] Tue, 12 February 2013 14:37 Go to previous messageGo to next message
BlackSwan
Messages: 21951
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/
Re: Single-row subquery Issue [message #577149 is a reply to message #577139] Tue, 12 February 2013 15:23 Go to previous messageGo to next message
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...?
Re: Single-row subquery Issue [message #577150 is a reply to message #577149] Tue, 12 February 2013 15:28 Go to previous messageGo to next message
Littlefoot
Messages: 18833
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.
Re: Single-row subquery Issue [message #577151 is a reply to message #577149] Tue, 12 February 2013 15:32 Go to previous message
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!
Previous Topic: NVL2 problem when using forms
Next Topic: logic to check record exist in tabl
Goto Forum:
  


Current Time: Sun Apr 20 15:22:13 CDT 2014

Total time taken to generate the page: 0.24103 seconds