Wednesday, November 23, 2011

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

Today, there a user requested that I extract out financial transaction ids that is not similar between to unique fields (as per 1st script).

Then I encounter error message : Error: ORA-01427: single-row subquery returns more than one row

1st script
------------------------------------------
select "/BA1/C40FTRAN"
from
sapsr3."/1BA/HM_WRC6_320" where
"/BA1/CR0GROUP" = '4EC31BE03C8D00EFE1008000AC1F2886' AND
"/BA1/C40FTRAN" <> (
select "/BA1/C40FTRAN"
from
sapsr3."/1BA/HM_WRC6_320"
where "/BA1/CR0GROUP" = '4E86D812813200A7E1008000AC1F2886'
);

Due to this error, I have change the script to allow Multiple-row Sub-query Operators as per script 2

select "/BA1/C40FTRAN"
from
sapsr3."/1BA/HM_WRC6_320" where
"/BA1/CR0GROUP" = '4EC31BE03C8D00EFE1008000AC1F2886' AND
"/BA1/C40FTRAN" NOT IN (
select "/BA1/C40FTRAN"
from
sapsr3."/1BA/HM_WRC6_320"
where "/BA1/CR0GROUP" = '4E86D812813200A7E1008000AC1F2886'
);

For any comparison operator listed in the table shown below, the sub-query must return single row (in other words, the sub-query, must be a single-row sub-query, otherwise it will fail)
Single-row Sub-query Operators
Symbol Meaning
= equal
> greater than
>= greater than or equal
< less than
<= less than or equal
<> not equal
!= not equal

The operators in the following table can use multiple-row sub-queries:
Multiple-row Sub-query Operators
Symbol Meaning
IN equal to any member in a list
NOT IN not equal to any member in a list
ANY returns rows that match any value on a list
ALL returns rows that match all the values in a list

There are several approaches to resolve this, most popular one being changing the inequality operator to a multiple-row operators (say, if the operator is "=" than change it "IN", "ANY", "ALL", so that it does not matter if multiple rows are returned.).

No comments:

Post a Comment