[FIXED] Hibernate subquery problem with aggregate function

Issue

I'm trying to run this query within Hibernate (through JPA) but it throws

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 32

Any way to make this work or make it better ? I would not want to use OR clause because the query is much slower when using that.

SELECT SUM(s) as sum FROM (
SELECT count (ID) AS s
  FROM TABLE1 rr
 WHERE rr.status = 0
   AND (rr.risk = '1' AND rr.rate = '222' )
UNION ALL
SELECT count (ID) AS s
  FROM TABLE1 rr
 WHERE rr.status = 0
   AND (rr.risk = '2' AND rr.open  = '222' ))

Solution

With HQL queries, Hibernate ORM doesn't support subqueries in the FROM clause (you can use them in the WHERE clause).

If you cannot rewrite this query as HQL without the subquery (for performance reasons, for example), I think it's OK to run it on the db as a native SQL query:

String sqlQuery = ...;
session.createNativeQuery(sqlQuery).getSingleResult();

Answered By - Davide
Answer Checked By - Cary Denson (JavaFixing Admin)