ALIAS is not allowed in HAVING clause, GROUP BY and WHERE clause. Allowed in ORDER BY

Alias is not allowed in HAVING clause and this is why.

Again, we have to consider how Oracle parses the SQL statement:

1)FROM Clause
2)WHERE Clause
3)GROUP BY Clause
4)HAVING Clause
5)SELECT
6)ORDER BY Clause

As you can see, the SELECT statement is processed in the end. All aliases are simply not known yet before that point. Therefore you can’t use ALIAS in HAVING clause. You should always use values from select statement instead. In our case, you should have used sum(salary) with HAVING clause

or department_id could also be used with HAVING clause.

In other words, HAVING clause will accept any argument that is present in the SELECT statement. The purpose of HAVING clause is to limit GROUP BY results. Since GROUP BY results will produce a set of data based on SELECT statement only, then HAVING clause will use this set of data as a source for further limitation.

Alias is not allowed in GROUP BY clause

For the same reason, ALIAS is not allowed in the GROUP BY clause as well. It is simply not known yet to Oracle compiler because the SELECT statement has not been processed yet.

Alias is not allowed in WHERE clause

For the same reason, ALIAS is not allowed in the WHERE clause as well. It is simply not known yet to Oracle compiler because the SELECT statement has not been processed yet.