Cannot run Hana Sql query with alias in Having clause

[prashantsoni]

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?

[pfefferf · Accepted solution]

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.

[prashantsoni · Accepted solution]

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?

[pfefferf · Accepted solution]

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