We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Calculated column based on other calculated column — Vertica Forum

Calculated column based on other calculated column

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"

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited July 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)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file