Can't use column aliases in the calculation of derived colum

[maheshsanapala]

Hi,

When i run sample query like below, it says Column1 NOT FOUND. However, i can rewrite the same thing by using sub query and it works fine too but it's quite painful when i write big queries. Doesn't VERTICA support this? OR i'm missing something? Any help would be greatly appreciated.

SELECT a+b AS column1
Column2,
Column1/Column2 AS Column3,
CASE WHEN Column3=0 THEN 0 ELSE Column2/Column3 END AS Column4
FROM
Vertica_Table;

Thanks
Mahesh.

[NorbertKrupa]

I would be surprised to see what DBMS actually supports that because of the way that fields are evaluated in the SELECT statement. You could explicitly define the calculation for your aliased columns, or use a subquery:

Code: Select all

SELECT CASE 
         WHEN ( ( a + b ) / column2 ) = 0 THEN 0 
         ELSE column2 / ( ( a + b ) / column2 ) 
       END AS Column4 
FROM   vertica_table; 

Code: Select all

SELECT CASE 
         WHEN column1 / column2 = 0 THEN 0 
         ELSE column2 / ( column1 / column2 ) 
       END AS column4 
FROM   (SELECT a + b AS column1, 
               column2 
        FROM   vertica_table) AS a;

[maheshsanapala]

Teradata supports this. It's an existing code that has been working since ages. We are trying to migrate to Vertica due to performance issues and it does not work in vertica.

[NorbertKrupa]

Well then; I now know of one platform that supports this.

While it would be easy for me to blast Teradata, I would rather put the blame on the individual that wrote the query. It's not ANSI standard SQL and just because Teradata extends functionality to evaluate columns more than once in the SELECT clause doesn't make it good practice.

[maheshsanapala]

Well, i got my answer. Vertica does not support this. Thanks for your response.

[id10t]

Hi!

[DELETED]

[maheshsanapala]

Hi Skwa,

As mentioned before, Teradata supports that kind of SQL .

Thanks
Mahesh