[FIXED] JPA/hibernate subquery in from clause

Issue

We're using JPA with hibernate as the provider, we have a query that contains a join with a subquery in the FROM clause, but we get the following error:

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 75 [SELECT sd FROM com.hp.amber.datamodel.entities.analysis.SnapshotDates sd, (SELECT max(x.changeDate) maxChangeDate, x.viewId, x.state FROM com.hp.amber.datamodel.entities.analysis.SnapshotDates x WHERE x.changeDate<:date AND x.viewId in (:viewIds) AND x.state=:state GROUP BY x.viewId, x.state) sd2 WHERE sd.viewId = sd2.viewId AND sd.state = :state AND sd.changeDate = sd2.maxChangeDate]

This is the query:

SELECT sd 
FROM SnapshotDates sd, 
     (SELECT max(x.changeDate) maxChangeDate, x.viewId, x.state 
      FROM SnapshotDates x
     WHERE x.changeDate<:date AND x.viewId in (:viewIds) AND x.state=:state
GROUP BY x.viewId, x.state) sd2
WHERE sd.viewId = sd2.viewId 
      AND sd.state = :state 
      AND sd.changeDate = sd2.maxChangeDate

Thank you for helping

Solution

I did not think HQL could do subqueries in the from clause

https://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch16.html#queryhql-subqueries

note the sentence:

Note that HQL subqueries can occur only in the select or where clauses.

I imagine you could change it to a native query and execute it that way.

Answered By - dispake
Answer Checked By - Senaida (JavaFixing Volunteer)