1

We are getting syntax error in group by clause while using group by with case statement in JPA named queries. We are using openjpa. Query looks like this(ids is a list in mentioned query)

select r.city,case when r.name='test' then 'T' else 'N' end as opt from testable r where r.id in (:ids) group by r.city,case when r.name='test' then 'T' else 'N' end

alok
  • 11
  • 4
  • Show the error, and you might want to simplify things while trying them out; if you know the :IDS works, create a simple query that doesn't need to filter on the list so we/you can focus on the group by logic. Or try the query without the group by etc. – Chris Sep 28 '21 at 20:20

1 Answers1

1

Try something like:

String jpqlQueryString = "select  r.city,"
+ " case when r.name='test' then 'T' else 'N' end as opt "
+ " from testable r "
+ " group by r.city, opt";

This should work, and then you can add in criteria to filter on.

Chris
  • 20,138
  • 2
  • 29
  • 43
  • We have tried above query, it is showing query is invalid, your select and having clauses must only include aggregates or values that appear in your grouping clause. I guess alias name for case statement causing issue. – alok Sep 29 '21 at 08:09