cancel
Showing results for 
Search instead for 
Did you mean: 

Alias column usage in function as parameter

Former Member
0 Kudos

Dear community,

I am struck up with this error, "invalid column name(alias column- PEXP)"  which is not recognised by hana interpreter, when used in below sql statement of hana modelling.

Select PROD,CATEGORY,DOE,LAG(DOE,1,DOE) OVER (PARTITION BY CATEGORY ORDER BY DOE)

AS "PEXP" ,DAYS_BETWEEN(DOE,PEXP) FROM "TARAK"."EXP";


error message "invalid column name"

SAP DBTech JDBC: [260]: invalid column name: PEXP: line 2 col 29 (at pos 111)


DOE is the date column,which i used in function LAG() to generate PEXP alias column.

I need the no of days between the two date columns DOE and PEXP, so i used the DAYS_BETWEEN() function.

Accepted Solutions (1)

Accepted Solutions (1)

pfefferf
Active Contributor
0 Kudos

As for the most databases alias column names cannot be referenced in the same statement. You have to repeat either the expression or do sub selection like following:

SELECT PROD, CATEGORY, DOE, PEXP, DAYS_BETWEEN(DOE, PEXP) as "DaysBetween"

FROM

(

     SELECT PROD, CATEGORY, DOE, LAG(DOE,1,DOE) OVER (PARTITION BY CATEGORY     

     ORDER BY DOE) as "PEXP"

     FROM "TARAK"."EXP"

);

Regards,

Florian

Former Member
0 Kudos

Dear Florian,

Thank you for your reply.

I have tried with the below query the result was similar, can i know which query has better performance ?

SELECT PROD,CATEGORY,DOE,LAG(DOE,1,DOE) OVER (PARTITION BY CATEGORY  ORDER BY DOE) as "PEXP",

DAYS_BETWEEN(DOE,LAG(DOE,1,DOE) OVER (PARTITION BY CATEGORY   

     ORDER BY DOE)) AS "DaysBetween" FROM "TARAK"."EXP";

Answers (0)