Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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 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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.