How to do a Union SQL statement in HQL?

hibernate grails hql grails-controller
jason picture jason · Aug 24, 2011 · Viewed 42k times · Source

I'm trying to create a Union between two tables, using HQL (Hibernate Query Language). This SQL script works fine on my SQL server:

SELECT COUNT(DISTINCT linkedin_id) as test, school_name
FROM
(SELECT * FROM alum_education 
 UNION
 SELECT * FROM alum_connection_educations) AS UNIONS where school_name='some string'

the problem is, when i try to run it in grails like this:

     def countOfEdu = AlumEducation.executeQuery("select count (distinct linkedinId ) as countOfEdu, schoolName as SchoolName from (SELECT * FROM alumEducation UNION SELECT * FROM alumConnectionEducations) AS UNIONS where schoolName='some string'" )

i get this error:

  org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 83 [select count(distinct linkedinId )  as countOfEdu, schoolName as SchoolName  from (SELECT * FROM alumEducation UNION SELECT * FROM alumConnectionEducations) AS UNIONS where schoolName='Duquesne University']

How can I run the above SQL statement in grails?

thanks jason

Answer

Andrey Adamovich picture Andrey Adamovich · Aug 24, 2011

Unions are not supported by HQL. There is an issue in Hibernate's JIRA that is open since 2005.

Related questions

No data type for node: org.hibernate.hql.internal.ast.tree.IdentNode HQL

I have the HQL where I trying to get artifacts that have no classification (when active is 0) artifacts = Artifact.findAll("FROM Artifact WHERE id NOT IN ( SELECT artifact_id FROM Classification WHERE active = 1) AND document_id = :docid",[docid:document.id], […

Read More
Hibernate HQL Count Distinct not working?

I have the following classes: class User { hasMany = [ratings: Rating] } class Item { hasMany = [ratings: Rating] } class Rating { belongsTo = [user: User, item: Item] } I want to count the distinct users that rated on an item. The following does not work: select …

Read More
Found shared references to a collection org.hibernate.HibernateException

I got this error message: error: Found shared references to a collection: Person.relatedPersons When I tried to execute addToRelatedPersons(anotherPerson): person.addToRelatedPersons(anotherPerson); anotherPerson.addToRelatedPersons(person); anotherPerson.save(); person.save(); My domain: Person { static hasMany = [relatedPersons:Person]; } any idea …

Read More