Eclipse Community Forums: EclipseLink » how to Use UNION and Order by Clause in my JPQL Query

Home » Eclipse Projects » EclipseLink » how to Use UNION and Order by Clause in my JPQL Query(how to Use UNION and Order by Clause in my JPQL Query)

Show: Today's Messages :: Show Polls :: Message Navigator

how to Use UNION and Order by Clause in my JPQL Query [message #1649629]

Wed, 04 March 2015 12:24

Vijay Kumar

Messages: 6
Registered: May 2013

Junior Member

Hi,
I am using eclipse link as a JPA in my project. the current jar i am using is eclipselink-2.5.2.Coming to my Question,
I want to use Order by in UNION combination. My JPQL query looks like following

select req from Rqst req  
 WHERE (req.Applc =:SYSTEM_IDENTIFIER1 AND req.procesTyp  =:PROCESS_TYPE1 AND UPPER(req.updtBy) =:UPDATED_BY1) 
 AND req.stat <> :ignoreDeletedRequests 
UNION 
select req from Rqst req 
 WHERE (req.Applc =:SYSTEM_IDENTIFIER2 AND req.procesTyp  =:PROCESS_TYPE2 AND UPPER(req.updtBy) =:UPDATED_BY2) 
 AND req.stat <> :ignoreDeletedRequests 
UNION 
select req from Rqst req 
 WHERE (req.Applc =:SYSTEM_IDENTIFIER3 AND req.procesTyp  =:PROCESS_TYPE3 AND UPPER(req.updtBy) =:UPDATED_BY3) 
 AND req.stat <> :ignoreDeletedRequests 
 ORDER BY req.rqstId ASC

With out Order by it is working for me. But when i use Order by I am getting The query contains a malformed ending.

Can any help me here i am stuck for long time.

Report message to a moderator

Re: how to Use UNION and Order by Clause in my JPQL Query [message #1655072 is a reply to message #1649629]

Sat, 07 March 2015 00:06

Will Dazey

Messages: 10
Registered: February 2015

Junior Member

I looked into this problem and it looks like a bug in the EclipseLink JPQL parser. Basically, what happens is that this query parses the UNION clauses and regards whatever is left as superfluous and unknown in the SelectStatement. The query ends up getting parsed into the following structure (order here dictates parse order too):

org.eclipse.persistence.jpa.jpql.parser.SelectStatement
     -org.eclipse.persistence.jpa.jpql.parser.SelectClause: select
     -org.eclipse.persistence.jpa.jpql.parser.FromClause: from
     -org.eclipse.persistence.jpa.jpql.parser.WhereClause: where
     -org.eclipse.persistence.jpa.jpql.parser.OrderByClause: orderBy
     -org.eclipse.persistence.jpa.jpql.parser.CollectionExpression: unions
          -org.eclipse.persistence.jpa.jpql.parser.UnionClause
               -org.eclipse.persistence.jpa.jpql.parser.SimpleSelectStatement: query
          -org.eclipse.persistence.jpa.jpql.parser.UnionClause
               -org.eclipse.persistence.jpa.jpql.parser.SimpleSelectStatement: query

Now, if you look at the order of the parsing, you see that the ORDER BY clause is parsed first, followed by the UNIONs. After parsing the UNIONs is done and returns, there are no further checks for an ORDER BY clause. I suggest you open a new EclipseLink bug for this problem and it may get more attention.

As a work around, can you rewrite your query using joins? Or, if performance isn't an issue, query without ORDER BY and order the resultset afterwords?

Report message to a moderator

Re: how to Use UNION and Order by Clause in my JPQL Query [message #1678562 is a reply to message #1655072]

Mon, 16 March 2015 08:41

Vijay Kumar

Messages: 6
Registered: May 2013

Junior Member

Thank you Will Dazey,

For your time and efforts. It was really helpful in getting the knowledge on how the query is getting parsed. Since we wanted to use UNION in place of OR for getting better performance as suggested by DB team.

So i have gone for native Query Option here.

this.entityManager.createNativeQuery(multiCriertionQuery.toString(),Rqst.class);

This worked for me. But if eclipse link resolves this error where we can avoid using native query, since it is difficult to construct when u are playing your query with 6 to 7 tables for getting results.

Report message to a moderator