Hi,
IMHO… I believe it is standard SQL behavior not to be able to reference a column alias in the same SELECT clause in which it was defined. I know that some databases allow this (i.e. MySQL), but databases like those were created by developers who coded in these types of work-arounds for their convenience. Enterprise class databases like Vertica, SQL Server and Oracle do not allow for this to protect against ambiguity.
Example:
We can’t reference the alias “a” in the SELECT.
Code: Select all
dbadmin=> SELECT * FROM test;
x
---
1
2
3
2
2
(5 rows)
dbadmin=> SELECT x, x+1 AS a, a+1 AS b FROM test;
ERROR 2624: Column "a" does not exist
But what if we could? In the following example, which column aliased as “a” would the user want Vertica to use when calculating “b”? The first, second or third one?
Code: Select all
dbadmin=> SELECT x, x+1 AS a, x+2 AS a, x+3 AS a FROM test;
x | a | a | a
---+---+---+---
1 | 2 | 3 | 4
2 | 3 | 4 | 5
3 | 4 | 5 | 6
2 | 3 | 4 | 5
2 | 3 | 4 | 5
(5 rows)
Thanks!