CODEWITHSUNDEEP
springoracle-databasespring-boothibernatejpa
Toan Dao
Toan Dao

Reputation: 571

How to use alias in @Formula

So today I just run into a problem where @Formula is not working as I expected. Let say:

Database

year |  X  |  Y 
-----+-----+-----
2010 |  A  |  10
2011 |  A  |  20
2011 |  B  |  99
2009 |  C  |  30
2010 |  C  |  40


I want to get the year with the highest 'Y' and group by 'X', so I write a query like this

SELECT year
FROM (SELECT
        year,
        MAX(y) OVER (PARTITION BY x ) max_y
      FROM TableB) q
WHERE year = '2011' and x = 'A';


So the result is

year |
-----+
2011 |


Then I want to use it in entity so I create entity with @Formula

@Formula("(SELECT year FROM (SELECT b.year, MAX(b.y) OVER(PARTITION BY b.x) max_y FROM TableB b) q WHERE year = '2011' AND x = 'A')")
private String yearWithHighestScore;

But Hibernate processes into:

(select entity0_.year as year1_8_ 
from (select b.year as name, max(b.y) over(partition by b.year) entity0_.max_y from TableB b) entity0_.q 
where entity0_.year=? and entity0_.x=?) as formula0_1_

You see Hibernate convert it into "entity0_.max_y" which is not correct because max_y is not a column of table TableB; it just an alias of calculated column

My question: Can we have any way to tell Hibernate it is alias of the column, it's not a column name itself ?

Thanks

Upvotes: 10

Views: 2189

Answers (3)

Saad
Saad

Reputation: 173

It is hibernate bug and it is been raised on their bug reports.

you can follow up with this bug on the link https://hibernate.atlassian.net/browse/HHH-15405

Upvotes: 1

psaraj12
psaraj12

Reputation: 5072

One way is to use a oracle view like the below which converts the alias into a column

 create or replace view TABLE_V as 
 SELECT
    year,
    MAX(y) OVER (PARTITION BY x ) max_y,
    x
  FROM TableB ;

and then use

SELECT year from TABLE_V where year='2011' and x='A';

Upvotes: 0

pdorgambide
pdorgambide

Reputation: 1867

Prefix alias name with '_':

@Formula("(SELECT year FROM (SELECT b.year, MAX(b.y) OVER(PARTITION BY b.x) _max_y FROM TableB b) _q WHERE year = '2011' AND x = 'A')")

Upvotes: 6

Related Questions