Error "ORA-00913: Too Many Values" when running SQL query

Question

I get the error "too many values" and I'm not sure how to approach it. The problem starts with 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 at the information about this error , and I believe it because the subquery in WHERE returns too many columns. The following are table data. But how can I destroy it? 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 

man

 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 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 

Answer 1

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 (for connections, corrections are likely to be needed):

 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' ) 

---

The problem is that your code has no relation to the person table. I think you should have one in the subqueries, which makes them correlated.

Answer 2

use "exists" in place "in" can help you

Answer 3

 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); 

when inserting values, it gives an error for many values

I have a script table for this table, when I insert it, it gives a lot of errors, and it works fine on my desktop