CODEWITHSUNDEEP
sqloracle-databasewith-statementwhere-in
Viacheslav Kroilov
Viacheslav Kroilov

Reputation: 1671

SQL syntax error with "WITH" clause

Got a strange problem using WITH alias in a WHERE clause. Problem SQL statement:

WITH CROOM AS (SELECT id FROM classroom WHERE floor = 1)
SELECT DISTINCT teacher_id
FROM timetable
WHERE classroom_id IN CROOM;

The error is ORA-00904: "CROOM": invalid identifier

While both of those semantically similar statements work great.

SELECT DISTINCT teacher_id
FROM timetable
WHERE classroom_id IN (SELECT id FROM classroom WHERE floor = 1);

WITH CROOM AS (SELECT id FROM classroom WHERE floor = 1)
SELECT DISTINCT T.teacher_id 
FROM timetable T JOIN CROOM ON (T.classroom_id = CROOM.id);

Why can't it recognize an alias in WHERE while accepts it in a FROM clause?

Upvotes: 2

Views: 373

Answers (1)

Mauri
Mauri

Reputation: 1245

you should use CROOM as a table. you can write for example:

WITH CROOM AS (SELECT id FROM classroom WHERE floor = 1)
SELECT DISTINCT teacher_id
FROM timetable
WHERE classroom_id IN (select id from CROOM);

Upvotes: 3

Related Questions