Skip to Content
author's profile photo Former Member
Former Member

Alias column usage in function as parameter

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Sep 15, 2016 at 09:36 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.