Skip to Content
0

Cannot run Hana Sql query with alias in Having clause

Feb 09, 2017 at 07:50 AM

94

avatar image
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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Florian Pfeffer
Feb 09, 2017 at 02:27 PM
1

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.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

So, it is not supported by HANA.

But it works with other sql's such as MySql ( Example ).

The above query is re-written form of:

  SELECT COL1, COL2 FROM TABLE1 WHERE ID = 1 and COL4 = 1415 AND COL3 IN (SELECT MAX(COL3) FROM TABLE1 WHERE ID = 1 and COL4 = 1415);

So, is there a way in HANA to re-write this query in a better way so as to avoid the sub query when the filters are same as in the main query?

0

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);
1