Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
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)
icon7.gif  how to Use UNION and Order by Clause in my JPQL Query [message #1649629] Wed, 04 March 2015 12:24 Go to next message
Vijay Kumar is currently offline Vijay KumarFriend
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.
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 Go to previous messageGo to next message
Will Dazey is currently offline Will DazeyFriend
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?
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 Go to previous messageGo to next message
Vijay Kumar is currently offline Vijay KumarFriend
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.

Re: how to Use UNION and Order by Clause in my JPQL Query [message #1824368 is a reply to message #1649629] Tue, 14 April 2020 13:13 Go to previous message
Ali Y. Akgul is currently offline Ali Y. AkgulFriend
Messages: 1
Registered: April 2020
Junior Member
I have exactly the same problem, sorry for writing this topic after long time, is there any solutions for this yet?
Previous Topic:Pdf link in Understanding EclipseLink Concepts 2.7 pointing to 2.6
Next Topic:Classes with @MappedSuperclass use a lot of memory
Goto Forum:
  


Current Time: Fri Mar 29 12:14:43 GMT 2024

Powered by FUDForum. Page generated in 0.02244 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top