How to use GROUP BY with UNION in JPQL

Question

I would like generate some data with JPQL for my output in Primefaces p:dataTable. I use following query.

Query query = this.em.createQuery("SELECT a FROM ProffesorTable a WHERE 
  a.fkProffesorID.name LIKE '"+buffervarQuery+"%' AND a.fkProffesorID.release='TRUE' 
UNION  
(SELECT a FROM ProffesorTable a WHERE a.fkProffesorID.name LIKE '%"+buffervarQuery+"'
  AND a.fkProffesorID.release='TRUE') GROUP BY a.fkProffesorID.name ");

result = query.getResultList();

I receive following Exception

Caused by: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Internal problem encountered while compiling
[SELECT a FROM ProffesorTable a WHERE a.fkProffesorID.name LIKE 'sie%' AND a.fkProffesorID.release='TRUE' UNION
(SELECT a FROM ProffesorTable a WHERE a.fkProffesorID.name LIKE '%sie' AND a.fkProffesorID.name='TRUE') GROUP BY a.fkProffesorID.name ")]. [249, 277] The query contains a malformed ending. at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1585) at com.sun.enterprise.container.common.impl.EntityManagerWrapper.createQuery(EntityManagerWrapper.java:456)

UNIONS are not supported in JPA. Check [this answer][http://stackoverflow.com/questions/17050589/union-in-jpa-query-from-the-same-table] for alternative solutions. — V G, Feb 11 '14 at 12:05
thank you for your answer. That is not good we didn't use EclipseLink gives there an alternative solution for this question query. — murat, Feb 11 '14 at 12:09

score 0 · Answer 1 · answered Feb 11 '14 at 15:23

Your simple query can be convert into this one and that supported by jpql as well.

SELECT a FROM ProffesorTable a
  WHERE (a.fkProffesorID.name LIKE '"+buffervarQuery+"%' or a.fkProffesorID.name LIKE '%"+buffervarQuery+"')
    AND a.fkProffesorID.release='TRUE' 
  GROUP BY a.fkProffesorID.name

How to use GROUP BY with UNION in JPQL

1 Answers1