CODEWITHSUNDEEP
sqloracle-database
Devon F
Devon F

Reputation: 73

Invalid identifier order by sql command after a union

I have tried to find a similar question but I can't. We are just trying to do a simple query with a union, and we get the 'invalid identifier' error on the final line. Does anyone know how to organize the parenthesis to make this valid? We have tried so many ways. 

SELECT DISTINCT E.CID
FROM Enrollments E, Students S
WHERE E.SID = S.SID AND (S.Major <> 'CS' OR S.Major IS NULL) 
GROUP BY E.CID
HAVING 10 > COUNT (*)
UNION SELECT DISTINCT C.CID
              FROM COURSES C
              MINUS 
       SELECT DISTINCT E3.CID 
              FROM Enrollments E3, Students S2
              WHERE E3.SID = S2.SID 
              GROUP BY E3.CID
              HAVING 0 < COUNT (*)
ORDER BY E.CID DESC;

error message :

ERROR at line 14:

ORA-00904: "E"."CID": invalid identifier

EDIT: Just so you know what the purpose is, all classes with less than 10 non CS majors along with all empty classes.

Upvotes: 2

Views: 592

Answers (1)

VBoka
VBoka

Reputation: 9083

  1. You are selecting E.CID
  2. Then you are creating an union with another query where you are selecting C.CID
  3. In the order by clause you have a result where the column is named CID and not E.CID

  4. And to add to this, as The Impelar comented, regarding the column E.CID in the group by clause:

    "E.CID does not exist at the level of the last line. It's exists in the scope of the first query only (lines 1-5)"

    SELECT DISTINCT E.CID
FROM Enrollments E, Students S
WHERE E.SID = S.SID AND (S.Major <> 'CS' OR S.Major IS NULL) 
HAVING 10 > COUNT (*)              
GROUP BY E.CID  
UNION SELECT DISTINCT C.CID
             FROM COURSES C
      MINUS 
      SELECT DISTINCT E3.CID 
             FROM Enrollments E3, Students S2
             WHERE E3.SID = S2.SID 
       GROUP BY E3.CID
       HAVING 0 < COUNT (*)
ORDER BY CID DESC;

Here is a demo without error

Upvotes: 1

Related Questions