Page 1 of 1

Referencing a column alias in the same select list

Posted: Wed Apr 06, 2016 7:09 pm
by jane
Hi all,

Is it possible (like in MySQL) to reference a column alias in the same select statement in which it is defined? If not, is there a work around?

Code: Select all

dbadmin=> select x from test;
 x
---
 1
 2
 3
 2
 2
(5 rows)

dbadmin=> select x as a, a + 1 as b from test;
ERROR 2624:  Column "a" does not exist
The above example works fine in MySQL.

Re: Referencing a column alias in the same select list

Posted: Wed Apr 06, 2016 10:44 pm
by NorbertKrupa

Code: Select all

select a.x + 1 as b
from (select x from test) as a;

Re: Referencing a column alias in the same select list

Posted: Thu Apr 07, 2016 12:46 pm
by JimKnicely
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!

Re: Referencing a column alias in the same select list

Posted: Thu Apr 07, 2016 4:51 pm
by jane
Thanks guys!

Norbert - I knew about the sub - select option, but I was hoping to avoid that as I have a lot queries that need editing to do that.

Jim - I get it. We'll do better with our coding.

You guys are awesome :D