Calculated column based on other calculated column

noisebringer

Hi!
I'm using Vertica not for long time, but i still can't believe it's impossible to do something like
SELECT M + 5 AS Q, Q + 4 AS R, R + 2 AS U
because Vertica, as far as i can understand can't calculate column, which calculation is based on another calculated column.
So i can do something like
SELECT
R + 2 AS U
FROM
SELECT Q + 4 AS R
FROM
SELECT M + 5 AS Q
But, but..... But i don't know what to say it about.

I hope that i'm wrong and someone would tell me that i can query the first variant but i need to do .....
Or, at least, i hope that someone of developers would write something like:"Yes. It is. But in close future it will be solved"

Jim_Knicely

The only mainstream RDBMS I've seen in my career that allows for reuse of a calculated field in a SELECT column list is MySQL... A database created by developers who love code hacks

There are all kinds of work-arounds in an enterprise class RDMS like Vertica,... one being a SQL function...

dbadmin=> create table test (m int);
CREATE TABLE

dbadmin=> insert into test select 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> create function add_together (x int, y int) return int as
dbadmin-> begin
dbadmin-> return x + y;
dbadmin-> end;
CREATE FUNCTION

dbadmin=> select add_together(m, 5) q, add_together(add_together(m, 5), 4) r, add_together(add_together(add_together(m, 5), 4), 2) u from test;
 q | r  | u
---+----+----
 6 | 10 | 12
(1 row)