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' ))
CodePudding user response:
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();