sql - Understanding Oracle aliasing - why isn't an alias not recognized in a query unless wrapped in a second query?

Question

I have a query

SELECT COUNT(*) AS "CNT",
       imei
FROM   devices  

which executes just fine. I want to further restrict the query with a WHERE statement. The (humanly) logical next step is to modify the query followingly:

SELECT COUNT(*) AS "CNT",
       imei
FROM   devices
WHERE  CNT > 1 

However, this results in a error message ORA-00904: "CNT": invalid identifier. For some reason, wrapping the query in another query produces the desired result:

SELECT *
FROM   (SELECT COUNT(*) AS "CNT",
               imei
        FROM   devices
        GROUP  BY imei)
WHERE  CNT > 1  

Why does Oracle not recognize the alias "CNT" in the second query?

Answer 1

Because the documentation says it won't:

Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause but not other clauses in the query.

However, when you have an inner select, that is like creating an inline view where the column aliases take effect, so you are able to use that in the outer level.