My code is like shown below :
select col1,count(col2) as col7 from --some join operation group by col1 having col7 >= 3 -- replace col7 by count(col2) to make the code work
My code causes the error "Invalid column name 'col7' ". Why does this happen ? It seems illogical that SQL does not allow me to use col7 in the last line.
I am using SQL server express 2008
The HAVING
clause is evaluated before the SELECT
- so the server doesn't yet know about that alias.
First, the product of all tables in the FROM
clause is formed.
The WHERE
clause is then evaluated to eliminate rows that do not satisfy the search_condition.
Next, the rows are grouped using the columns in the GROUP BY
clause.
Then, groups that do not satisfy the search_condition
in the HAVING
clause are eliminated.
Next, the expressions in the SELECT
statement target list are evaluated.
If the DISTINCT
keyword in present in the select clause, duplicate rows are now eliminated.
The UNION
is taken after each sub-select is evaluated.
Finally, the resulting rows are sorted according to the columns specified in the ORDER BY
clause.
TOP
clause is executed.
Hope this answers your question. Also, it explains why the alias works in ORDER BY
clause.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With