Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 21 November 2019

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 enameename 
          FROM emp
         WHERE deptno = d.deptno)
  FROM dept d



Share this article :

0 comments:

Post a Comment