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)
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:
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