Resolved: ORA-00913 Too Many Values

ORA-00913 Too Many Values:

     This error will occur many cases here shown some causes ,occurs and solutions

Cause: 

     The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set.

Case 1:

     This error will occurs when create Subquery as shown below and it will return More than one value and it will through the error like ORA-00913 Too Many Values

SELECT * FROM EMP WHERE Deptno in (SELECT deptno,dname FROM Dept)

In this case subquery return more than one value (deptno,dname) but subquery need to return only one value(deptno) as shown below statement

Solution:

SELECT * FROM EMP WHERE Deptno in (SELECT deptno FROM Dept)

Case 2:

     This error will occurs when inserting data into table with multiple values as shown below and it will return More than one value and it will through the error like ORA-00913 Too Many Values

Insert into dept (deptno,dname) values (10,'Accounting','US')

In this case inserting data into table reference columns two (deptno,dname) but inserting values are three(10,'Accouing','US') return more than one value but values should be two (10,'Accouing') as shown below statement

Solution:

Insert into dept (deptno,dname) values (10,'Accounting')

Case 3:

     This error will occurs when scaler query as shown below and it will return More than one value and it will through the error like ORA-00913 Too Many Values

SELECT deptno,  dname, (SELECT ename  FROM emp WHERE deptno = d.deptno) ename FROM dept d

In this case scaler query return more than one value, One department having multiple employees you can show all employees by placing comma separated values as shown below by using list tag function

Solution:

SELECT deptno,

       dname,

       (SELECT listagg (ename, ',') WITHIN GROUP (ORDER BY ename) ename 

          FROM emp

         WHERE deptno = d.deptno)

  FROM dept d