Referencing a column alias in the same select list

Moderator: NorbertKrupa

Post Reply
jane
Newbie
Newbie
Posts: 8
Joined: Wed Apr 17, 2013 12:57 am

Referencing a column alias in the same select list

Post by jane » Wed Apr 06, 2016 7:09 pm

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.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Referencing a column alias in the same select list

Post by NorbertKrupa » Wed Apr 06, 2016 10:44 pm

Code: Select all

select a.x + 1 as b
from (select x from test) as a;
Checkout vertica.tips for more Vertica resources.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1768
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Referencing a column alias in the same select list

Post by JimKnicely » Thu Apr 07, 2016 12:46 pm

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!
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

jane
Newbie
Newbie
Posts: 8
Joined: Wed Apr 17, 2013 12:57 am

Re: Referencing a column alias in the same select list

Post by jane » Thu Apr 07, 2016 4:51 pm

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

Post Reply

Return to “New to Vertica SQL”