Understanding Oracle aliasing - why is it not an alias not recognized in the request if it is not enclosed in the second request?

Question

I have a request

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

which runs just fine. I want to further limit the query to the WHERE clause. The next step (humanly) is to modify the request as follows:

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

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

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

Why doesn't Oracle recognize the alias "CNT" in the second query?

Answer 1

The simple answer is that the AS clause determines which column will be called as a result, which is different from the query itself.

In your example, using the HAVING will work best:

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

Answer 2

As the documentation says it will not:

Specify an alias for the expression column. The Oracle database will use this alias in the result set column header. The keyword AS is optional. Elias effectively renames the selection list item for the duration of the request. An alias can use in order_by_clause, but not other offers in a request.

However, when you have an internal choice, it is like creating an inline view in which the aliases of the columns take effect, so you can use them externally.

Answer 3

I would suggest that an alias is not assigned to the result column until the WHERE clause is processed and the data is generated. Is Oracle different from other DBMSs in this behavior?

Answer 4

To summarize, this little gem explains:

10 Simple Steps to Understanding SQL

A common source of confusion is the simple fact that the syntax of SQL elements is not ordered as they are executed. Lexical order:

 SELECT [ DISTINCT ] FROM WHERE GROUP BY HAVING UNION ORDER BY 

For simplicity, not all SQL statements are listed. This lexical ordering is fundamentally different from the logical order, that is, execution:

 FROM WHERE GROUP BY HAVING SELECT DISTINCT UNION ORDER BY 

As a result, everything that you call using "AS" will be available only after WHERE , HAVING and GROUP BY have been completed.