Can't refer to column aliases in the WHERE clause Options

codeling Posts: 1109 Points: 4668
Posted: Tuesday, October 8, 2019 11:48:22 AM

If you refer column alias in the where caluse, you will get  the error:

SELECT YEAR(transaction_date) AS YEAR,amount FROM dev.sale

WHERE YEAR=2019

You get the error

Invalid column name ‘year’.

It is because the column alias are not immediately known to the WHERE clause.

 


codeling Posts: 1109 Points: 4668
Posted: Tuesday, October 8, 2019 11:49:37 AM

select * from 

(

SELECT YEAR(transaction_date) AS YEAR,amount FROM dev.sale

)

WHERE YEAR=2019


codeling Posts: 1109 Points: 4668
Posted: Tuesday, October 8, 2019 11:51:23 AM

But you can use column alias in the ORDER BY clause because ORDER BY is executed lastly after the entire column list is known.

SELECT YEAR(transaction_date) AS YEAR,amount FROM dev.sale

ORDER BY YEAR

Users browsing this topic
Guest