CODEWITHSUNDEEP
sqloracle-databaseoracle11gora-00913
Caffeinated
Caffeinated

Reputation: 12484

"ORA-00913: too many values" error when I run SQL query

I'm getting a "too many values" error and I'm not sure how to approach it. The problem starts at the first nested SELECT

SELECT p.name, p.phone_number 
    FROM person p WHERE 
    (SELECT ks.title, ks.ks_code 
    FROM required_skills rs JOIN knowledge_skills ks USING (ks_code) 
    WHERE rs.pos_code = 'CS1') 
    IN (SELECT ks.title, ks.ks_code 
        FROM courses_taken ct  JOIN course_learning_units clu 
        USING (c_code)  JOIN learning_unit_skills lus USING (lu_code) 
        JOIN knowledge_skills ks USING (ks_code))

I looked up info about this error and I believe it's because the subquery in the WHERE returns too many columns. The table data is below. But how can I whittle that down? I appreciate any pointers, Thanks

required_skills

POS_CODE    VARCHAR2(10 BYTE)   Yes     1   
KS_CODE VARCHAR2(10 BYTE)   Yes     2

learning_unit_skills

LU_CODE VARCHAR2(10 BYTE)   Yes     1   
KS_CODE VARCHAR2(10 BYTE)   Yes     2

person

PER_ID  NUMBER(38,0)    No      1   
NAME    VARCHAR2(255 BYTE)  Yes     2   
STREET  VARCHAR2(255 BYTE)  Yes     3   
CITY    VARCHAR2(60 BYTE)   Yes     4   
ZIP_CODE    VARCHAR2(60 BYTE)   Yes     5   
PHONE_NUMBER    VARCHAR2(255 BYTE)  Yes     6   
GENDER  VARCHAR2(6 BYTE)    Yes     7

course learning units

C_CODE  VARCHAR2(10 BYTE)   Yes     1   
LU_CODE VARCHAR2(10 BYTE)   Yes

courses taken

C_CODE  VARCHAR2(10 BYTE)   Yes     1   
PER_ID  NUMBER(38,0)    Yes     2

Upvotes: 5

Views: 52197

Answers (3)

sandeep chouhan
sandeep chouhan

Reputation: 1

CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));


INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);

while inserting values it is giving error to many values

I've a table script of this table when I insert it it gives lot of errors while on my desktop it works fine

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

A quick rewrite:

SELECT p.name, p.phone_number 
    FROM person p WHERE EXISTS
    (SELECT * 
    FROM required_skills rs JOIN knowledge_skills ks USING (ks_code) 
    WHERE rs.pos_code = 'CS1'
      AND (ks.title, ks.ks_code)
    IN (SELECT ks.title, ks.ks_code 
        FROM courses_taken ct  JOIN course_learning_units clu 
        USING (c_code)  JOIN learning_unit_skills lus USING (lu_code) 
        JOIN knowledge_skills ks USING (ks_code))
    )

or (the joins will probably need corrections):

SELECT p.name, p.phone_number 
    FROM person p WHERE EXISTS
    (SELECT * 
     FROM required_skills rs JOIN knowledge_skills ks USING (ks_code) 
        JOIN ( courses_taken ct  JOIN course_learning_units clu 
               USING (c_code)  JOIN learning_unit_skills lus USING (lu_code) 
              ) USING (ks_code)
     WHERE rs.pos_code = 'CS1'
    )

Problem is that in your code there is no relation to table person. I guess you should have one in the subqueries - making them correlated.

Upvotes: 6

Rashmi Kant Shrivastwa
Rashmi Kant Shrivastwa

Reputation: 1157

use "exists" at the place of "in" may help you

Upvotes: 1

Related Questions