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)

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

Wed, 04 March 2015 07:24

Eclipse User

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]

Fri, 06 March 2015 19:06

Eclipse User

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 04:41

Eclipse User

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 09:13

Eclipse User

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:

-=] Back to Top [=-

Current Time: Fri Oct 31 12:44:43 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter