How can column alias be used in the function

[Former Member]

Dear Community,

My intention was to get the number of days between DOE(date) and PEXP(which is a alias column generated by using lag function on DOE)

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

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

but it prompts an error message "invalid column name"

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

can you please help me to resolve this?

[reimer_pods]

Hi,

even if I don't get the syntax of your statement, IMHO the alias should be put after the function call.

select PROD,CATEGORY,DOE,LAG(DOE,1,DOE) AS "PEXP",

DAYS_BETWEEN(DOE-PEXP)

OVER (PARTITION BY CATEGORY ORDER BY DOE)

FROM "TARAK"."EXP";

The OVER clause requires calling an aggregate function, if my understanding is correct.

Here's a comprehensive example for statements of that type:

http://sqlanywhere.blogspot.de/2011/11/olap-window-for-running-balance.html

[Former Member]

Hi Reimer Pods,

I would breif my sql statement which i used in hana studio modeling prespective,to make an better understanding.

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

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

DOE is the date column,which i used in 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.

but as PEXP is an alias column which doesn't exist physically in Table"TARAK"."EXP", it prompts error message invalid column name, when i execute the above statement.

hope i am clear now.

[former_member194571]

Hi Paruchuru,

is this a query you're trying to run against HANA?

I wasn't aware of functions LAG or DAYS_BETWEEN in SQL Anywhere (and found them in the HANA manuals, now that you mentioned HANA Studio), and while DAYS_BETWEEN might be a user defined function, we don't have window aggregate UDFs in SQL Anywhere.

In this case, you might be better of posting this in a different forum.

BTW, DAYS_BETWEEN (if it's the HANA function) demands two parameters separated by comma, so replacing "DAYS_BETWEEN(DOE-PEXP)" by "DAYS_BETWEEN(DOE, PEXP)" might be at least part of the solution to your problem.

HTH anyway

Volker

[Former Member]

Hi Volker,

Thanks for your reply.

yeah DAYS_BETWEEN(DOE, PEXP)"  is the right syntax,but still alias column is not identified.

I would try to post in hana sql forum.