Alias is not allowed in HAVING clause and this is why.
Again, we have to consider how Oracle parses the SQL statement:
3)GROUP BY Clause
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.
Alias is allowed in ORDER BY clause only
By the time the Oracle compiler will get to the ORDER BY clause, all aliases are already known. This is why they can be used at this point.