I did 3 different queries using with clause:
WITH non_max_ratings as ( SELECT rating FROM sailors WHERE rating<(SELECT MAX(rating) FROM sailors) ) SELECT rating FROM non_max_ratings nmr WHERE rating=(SELECT max(rating) FROM nmr); WITH non_max_ratings as ( SELECT rating FROM sailors WHERE rating<(SELECT MAX(rating) FROM sailors) ) SELECT rating FROM non_max_ratings nmr WHERE rating in nmr; WITH non_max_ratings as ( SELECT rating FROM sailors WHERE rating<(SELECT MAX(rating) FROM sailors) ) SELECT rating FROM non_max_ratings nmr WHERE rating in nmr.rating;
These queries are just meaningless test stataments so don’t bother with their function.
The 1st query had compilation error:
WHERE rating=(SELECT max(rating) FROM nmr) * ERROR at line 145: ORA-00942: table or view does not exist
The 2nd query had another error:
WHERE rating in nmr * ERROR at line 154: ORA-00904: "NMR": invalid identifier
And the 3rd query ran successfully.
But according to what I’ve learned, all of them should perform correctly as derived relations defined using with clause can be accessed anywhere in the same query. So why?
Advertisement
Answer
1 You can’t access the alias from the outer query.
SELECT rating FROM non_max_ratings nmr WHERE rating=(SELECT max(rating) FROM nmr);
instead simply use
SELECT rating FROM non_max_ratings nmr WHERE rating=(SELECT max(rating) FROM non_max_ratings);
2 This is not valid syntax
SELECT rating FROM non_max_ratings nmr WHERE rating in nmr;
in requires a comma separated list of values like
rating in ('value1', 'value2')