Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » case insensitive "order by"(case insensitive "order by")
case insensitive "order by" [message #496212] Mon, 09 November 2009 11:34 Go to next message
Ashish  is currently offline Ashish Friend
Messages: 13
Registered: September 2009
Junior Member
Hello,

I am using eclipse link 1.1.2 version.
I have to perform case insensitive sorting. I sql I can write query like:
select * from tenant order by upper(name) asc;

but we I try to write euivalent JPQL query:
select tenant from TenantEntity tenant order by upper(tenant .name) asc;

i got exception:
java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager
"syntax error at [upper]."

but upper is working for where claue.

So I can write query like:
select tenant from TenantEntity tenant where upper(tenant.name) like 'G%"

but not with order by.

My question is: Does "order by" not supporting upper\lower function? If not how can I achieve it?

Thanks in advance.
Ashish
Re: case insensitive "order by" [message #496290 is a reply to message #496212] Mon, 09 November 2009 15:51 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

This is a limitation of JPQL.

See,

https://bugs.eclipse.org/bugs/show_bug.cgi?id=219814

As a workaround you can use a EclipseLink Expression query, or a native SQL query.


James : Wiki : Book : Blog : Twitter
Re: case insensitive "order by" [message #496444 is a reply to message #496290] Tue, 10 November 2009 06:52 Go to previous messageGo to next message
Ashish  is currently offline Ashish Friend
Messages: 13
Registered: September 2009
Junior Member
Hello James,

Thank you very much for your response.

But I have doubt, when we can achieve it through Expression api, why we can't achieve it through JPQL. I believe JPQL implementation for EclipseLink is developed by EclipseLink only and API development is also done by same.

So, when we have some implementation, why we can't reuse it for JPQL.

This question is just for curiosity. I definitely feel, there will be some constraints in doing it. I just want to know about those constraints.

Ashish
Re: case insensitive "order by" [message #497134 is a reply to message #496212] Thu, 12 November 2009 15:24 Go to previous message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

There is no reason why this could not be supported in JPQL, please vote for the bug.


James : Wiki : Book : Blog : Twitter
Previous Topic:EclipseLink persistance.xml Tomcat OSGI
Next Topic:@ReturnInsert(returnOnly=true) not working with PostgreSQL
Goto Forum:
  


Current Time: Thu Dec 18 22:35:03 GMT 2014

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

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