Query to use intermediate Value

Question

Hi I am trying to write a query to use the intermediate output.

I have a table with PROD_DATE, PRICE as columns, When I give the below query in HANA the query executes as expected.

select round(days_between( MIN(PROD_DATE) , MAX(PROD_DATE))/365) as yea,    
SUM(PRICE) as totalRevenue from AAA;

If I want to use the yea and totalRevenue columns calculated previously like below:

select round(days_between( MIN(PROD_DATE) , MAX(PROD_DATE))/365) as yea,    
SUM(PRICE) as totalRevenue, totalRevenue/yea as perc from AAA;

it gives me an error

ERROR is INVALID COLUMN NAME totalRevenue

Answer 1

Correct. You cannot use a value assigned in a SELECT again in the SELECT. You can repeat the expressions or use a subquery:

SELECT t.*, totalRevenue/yea
FROM (select round(days_between( MIN(PROD_DATE) , MAX(PROD_DATE))/365) as yea, 
             SUM(PRICE) as totalRevenue, totalRevenue/yea as perc
      from AAA
     ) t