“table or view does not exist” and “invalid identifier” errors with “with” clause

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?

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