Skip to Content

Cannot run Hana Sql query with alias in Having clause

SELECT COL1, COL2,MAX(COL3) as mx FROM TABLE1 WHERE ID = 1 
     and COL4 = 1415  having  mx=MAX(COL3);

When i run above query, i get the error SAP DBTech JDBC: [260]: invalid column name: MX.

I have tried using double quotes too, but same error. Does HANA Sql support use of alias name in having clause?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 09, 2017 at 02:27 PM

    No, it is not supported to use an alias name in a having clause. The reasons is that the alias is not known when the having clause is executed, cause the having clause is executed before the select clause.

    For instance you can visualize that in an easy way using the PlanViz tool.

    Add comment
    10|10000 characters needed characters exceeded

    • If you just wanna avoid the duplicate filter criteria, you can do it in following way for instance (but you have to consider the performance, w/o checking it in detail I would assume that the first statement will be faster):

      WITH t AS ( SELECT col1, col2, col3 FROM table1 WHERE id = 1 AND col4 = 1415 )
        SELECT col1, col2
        FROM t
        WHERE col3 = (SELECT max(col3) FROM t);