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