Misanjo Misanjo - 6 months ago 15
SQL Question

Reference a renamed variable with the same name in the source table

I'm trying to reference the new variable

DATE_YEAR
instead the original
DATE_YEAR
that I have in my Teradata database
TEST
.
How can i get it?
I found nothing in Teradata documentation.

SELECT DATE_YEAR+1 AS DATE_YEAR, COUNT(1)
FROM TEST
WHERE DATE_YEAR = 2016 GROUP BY 1;

Answer

Based on Standard SQL the columns in the SELECT-list are created after FROM/WHERE/GROUP BY/HAVING/OLAP, but before ORDER BY, so you can use an alias only in ORDER BY.

Due to historical reasons Teradata allows reusage of an alias in any place, this is very convenient as you don't have to cut&paste or use nested SELECTs. But there are some scoping rules, only when a column name is not found it's looked up in the alias-list. So the rough rule of thumb is: Never assign an alias which matches an existing column name and then you can easily use it in any place:

SELECT DATE_YEAR+1 AS DATE_YR, COUNT(1)
FROM TEST 
WHERE DATE_YR = 2016 GROUP BY 1;
Comments