Error
Trouble Shooting Error
Cause: The outer query must use one of the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row.
Action: Use ANY, ALL, IN, or NOT IN to specify which values to compare or reword the query so only one row is retrieved.
EXAMPLE
Trouble Shooting Error
Cause: The outer query must use one of the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row.
Action: Use ANY, ALL, IN, or NOT IN to specify which values to compare or reword the query so only one row is retrieved.
EXAMPLE
--The error is ORA-01427:
single-row subquery returns more than one row
DECLARE
xxdeptno NUMBER;
BEGIN
SELECT deptno
INTO xxdeptno
FROM scott.emp
WHERE deptno = --lokanadham
(SELECT deptno FROM scott.dept) AND ROWNUM < 2; --lokanadham
DBMS_OUTPUT.put_line
('The Department is ' || xxdeptno);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
('The Error is ' || SQLERRM);
END;
--solution put 'in' at
condtion
DECLARE
xxdeptno NUMBER;
BEGIN
SELECT deptno
INTO xxdeptno
FROM scott.emp
WHERE deptno in (SELECT deptno
FROM scott.dept) AND ROWNUM < 2;
DBMS_OUTPUT.put_line
('The Department is ' || xxdeptno);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('The Error is
' || SQLERRM);
END;
--SOLUTION----KEWORDS:ANY,
ALL, IN, or NOT IN
--Example:1.
SELECT deptno, ename
FROM scott.emp
WHERE deptno IN (SELECT deptno
FROM scott.dept
WHERE deptno = '10')
--Example:2.
SELECT ename
FROM scott.emp
WHERE deptno NOT IN (SELECT deptno
FROM scott.dept)
--Example:3.
--GIVES 14 RECORDS
SELECT deptno, ename
FROM scott.emp
WHERE deptno = ANY (SELECT deptno
FROM scott.dept)
--GIVES 3 RECORES
SELECT deptno, ename
FROM scott.emp
WHERE deptno = ANY (SELECT deptno
FROM scott.dept
WHERE deptno = '10')
--Example:4.
--GIVES 0 RECORDS
SELECT deptno, ename
FROM scott.emp
WHERE deptno = ALL (SELECT deptno
FROM scott.dept)
--GIVES 3 RECORDS
SELECT deptno, ename
FROM scott.emp
WHERE deptno = ALL (SELECT deptno
FROM scott.dept
WHERE deptno = '10')