How to use dynamic group by query in JPA

Asked Apr 02 '20 at 16:55

Active Apr 02 '20 at 17:07

Viewed 424 times

@Query("select new HiveHotelDateSummary(p.computeCategory, **case when 'CLUSTER'='CLUSTER' then p.clusterId else p.cityId end**,sum(p.addbackRooms),p.agreementTypeName,p.d) from HiveHotelDateSummary p where p.d>=:startDate  and p.d<=:endDate **group by p.computeCategory,case when 'CLUSTER'='CLUSTER' then p.clusterId else p.cityId end**,p.d,p.agreementTypeName")

When I am using the above query it works fine, but when I parametrized it doesn't work like in the below query.

select new HiveHotelDateSummary(p.computeCategory, case when :resourceType='CLUSTER' then p.clusterId else p.cityId end,sum(p.addbackRooms),p.agreementTypeName,p.d) from HiveHotelDateSummary p where p.d>=:startDate  and p.d<=:endDate group by p.computeCategory,case when :resourceType='CLUSTER' then p.clusterId else p.cityId end,p.d,p.agreementTypeName

anybody who can help me out?

edited Apr 02 '20 at 17:07

Florian

2,796
1
15
25

asked Apr 02 '20 at 16:55

Harshit Khandelwal

Did you try to invert the argument position like "when 'CLUSTER'=:resourceType" ? – Paulo Araújo Apr 02 '20 at 17:04
@PauloAraújo yes I tried but it didn't work. – Harshit Khandelwal Apr 02 '20 at 17:55
I am getting org.postgresql.util.PSQLException: ERROR: column "hivehoteld0_.cluster_id" must appear in the GROUP BY clause or be used in an aggregate function. – Harshit Khandelwal Apr 02 '20 at 18:01
Does this answer your question? [must appear in the GROUP BY clause or be used in an aggregate function](https://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function) – Nitin Bisht Apr 02 '20 at 18:15
No, I am getting that error – Harshit Khandelwal Apr 02 '20 at 18:21
Can you get the actual SQL command produced by the JPA implementation? You can do that defining log parameters on persistence.xml so you will be able to debug. – Paulo Araújo Apr 02 '20 at 20:37

How to use dynamic group by query in JPA

0 Answers0