ORA-01427: single-row subquery returns more than one row-Cursor help 2

[AlStl]

Following is the scenario:

CREATE TABLE TEST1
(
ITEM VARCHAR2 (12),
EQ_TYPE VARCHAR2 (4),
PRIMARY KEY (ITEM)
);


CREATE TABLE TEST2
(
ITEM VARCHAR2 (12),
JOBNUM VARCHAR2 (12),
EQ_GROUP_TYPE VARCHAR2 (20),
PRIMARY KEY (ITEM, JOBNUM)
);


INSERT INTO TEST1 (ITEM, EQ_TYPE) VALUES ('AAAA1', '1AES');

INSERT INTO TEST1 VALUES ('BBBB1', '5EXM');

INSERT INTO TEST1 VALUES ('CCCC1', 'D100');


INSERT INTO TEST2 VALUES ('AAAA1', 'XXXX1', NULL);
INSERT INTO TEST2 VALUES ('AAAA1', 'XXXX2', NULL);
INSERT INTO TEST2 VALUES ('AAAA1', 'XXXX3', NULL);

INSERT INTO TEST2 VALUES ('BBBB1', 'YYYY1', NULL);
INSERT INTO TEST2 VALUES ('BBBB1', 'YYYY2', NULL);
INSERT INTO TEST2 VALUES ('BBBB1', 'YYYY3', NULL);
INSERT INTO TEST2 VALUES ('BBBB1', 'YYYY4', NULL);
INSERT INTO TEST2 VALUES ('BBBB1', 'YYYY5', NULL);
INSERT INTO TEST2 VALUES ('BBBB1', 'YYYY6', NULL);
INSERT INTO TEST2 VALUES ('BBBB1', 'YYYY7', NULL);

INSERT INTO TEST2 VALUES ('CCCC1', 'ZZZZ1', NULL);
INSERT INTO TEST2 VALUES ('CCCC1', 'ZZZZ2', NULL);
INSERT INTO TEST2 VALUES ('CCCC1', 'ZZZZ3', NULL);
INSERT INTO TEST2 VALUES ('CCCC1', 'ZZZZ4', NULL);
INSERT INTO TEST2 VALUES ('CCCC1', 'ZZZZ5', NULL);
INSERT INTO TEST2 VALUES ('CCCC1', 'ZZZZ6', NULL);
INSERT INTO TEST2 VALUES ('CCCC1', 'ZZZZ7', NULL);

Following is the cursor code I wrote to update table TEST2.EQ_GROUP_TYPE based on case statement and join between TEST1 & TEST2 based on ITEM. But I am getting an error: ORA-01427: single-row subquery returns more than one row
ORA-06512: at line 19


Now, I know its returning more than one row...i just do not know how to fix it? Any help will be highly appreciated. In reality TEST1 & TEST2 contains lot more rows and this case statement has more variations.


DECLARE
CURSOR cur IS

SELECT A.ITEM
FROM TEST2 A, TEST1 B
WHERE A.ITEM = B.ITEM;

CURRENT_VALUE VARCHAR2(20);

to_commit NUMBER :=0;

BEGIN
OPEN cur;

FETCH cur INTO CURRENT_VALUE;

WHILE cur%FOUND
LOOP
UPDATE TEST2
SET EQ_GROUP_TYPE =
(SELECT CASE
WHEN B.EQ_TYPE = '1AES' THEN '1AE'
WHEN B.EQ_TYPE = '5EXM' THEN '5ES'
WHEN B.EQ_TYPE = 'D100' THEN 'DMS'
ELSE NULL
END
FROM TEST1 B, TEST2 A
WHERE (B.ITEM = A.ITEM ) AND (A.ITEM=CURRENT_VALUE) );

IF to_commit >= 1000
THEN
COMMIT;

to_commit := 0;
END IF;

to_commit := to_commit + 1;

FETCH cur INTO CURRENT_VALUE;
END LOOP;

COMMIT;

CLOSE cur;
END;

 

[taupirho]

SQL> select * from test1;

ITEM EQ_T
------------ ----
AAAA1 1AES
BBBB1 5EXM
CCCC1 D100

SQL> select * from test2
2 /

ITEM JOBNUM EQ_GROUP_TYPE
------------ ------------ --------------------
AAAA1 XXXX1
AAAA1 XXXX2
AAAA1 XXXX3
BBBB1 YYYY1
BBBB1 YYYY2
BBBB1 YYYY3
BBBB1 YYYY4
BBBB1 YYYY5
BBBB1 YYYY6
BBBB1 YYYY7
CCCC1 ZZZZ1

ITEM JOBNUM EQ_GROUP_TYPE
------------ ------------ --------------------
CCCC1 ZZZZ2
CCCC1 ZZZZ3
CCCC1 ZZZZ4
CCCC1 ZZZZ5
CCCC1 ZZZZ6
CCCC1 ZZZZ7

17 rows selected.

SQL> UPDATE TEST2 A
2 SET EQ_GROUP_TYPE =
3 (SELECT CASE
4 WHEN B.EQ_TYPE = '1AES' THEN '1AE'
5 WHEN B.EQ_TYPE = '5EXM' THEN '5ES'
6 WHEN B.EQ_TYPE = 'D100' THEN 'DMS'
7 ELSE NULL
8 END
9 FROM TEST1 B
10 WHERE B.ITEM = A.ITEM)
11 where exists (select 'x' from test1 where item = a.item)
12 /

17 rows updated.

SQL> select * from test2;

ITEM JOBNUM EQ_GROUP_TYPE
------------ ------------ --------------------
AAAA1 XXXX1 1AE
AAAA1 XXXX2 1AE
AAAA1 XXXX3 1AE
BBBB1 YYYY1 5ES
BBBB1 YYYY2 5ES
BBBB1 YYYY3 5ES
BBBB1 YYYY4 5ES
BBBB1 YYYY5 5ES
BBBB1 YYYY6 5ES
BBBB1 YYYY7 5ES
CCCC1 ZZZZ1 DMS

ITEM JOBNUM EQ_GROUP_TYPE
------------ ------------ --------------------
CCCC1 ZZZZ2 DMS
CCCC1 ZZZZ3 DMS
CCCC1 ZZZZ4 DMS
CCCC1 ZZZZ5 DMS
CCCC1 ZZZZ6 DMS
CCCC1 ZZZZ7 DMS

17 rows selected.


In order to understand recursion, you must first understand recursion.

 

[fredericofonseca]

taupirho gave you the solution but didn't explain why.

When you do your inner select as follows, you are joining test1 and test2 - this returns all the rows from both tables - in the sample below you get 3 rows back hence the error.
SELECT CASE
WHEN B.EQ_TYPE = '1AES' THEN '1AE'
WHEN B.EQ_TYPE = '5EXM' THEN '5ES'
WHEN B.EQ_TYPE = 'D100' THEN 'DMS'
ELSE NULL
END
FROM TEST1 B, TEST2 A
WHERE (B.ITEM = A.ITEM)
and a.item = 'AAAA1'

taupirho removed the the join on the inner SQL (as it was not required), and further more a exists was added to the update - required as otherwise you might get rows updated on table TEST2 that had no corresponding match on table TEST1.

As a hint for you whenever you have these type of errors always try the inner SQL's individually to see the results you get - most of the times it is a minor error and easily fixed.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[AlStl]

taupirho, thanks again!! I was on the wrong track following this cursor thing. Just a big overhead for nothing.

Frederico, thanks for the explanation.

Al