Skip to main content

Calculated column based on other calculated column

  • July 21, 2017
  • 1 reply
  • 2 views
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"

    1 reply

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • July 21, 2017

    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)
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement