Eclipse Community Forums: EclipseLink » case insensitive "order by"

Help

Home

Home » Eclipse Projects » EclipseLink » case insensitive "order by"(case insensitive "order by")

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

case insensitive "order by" [message #496212]

Mon, 09 November 2009 11:34

Ashish

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

Report message to a moderator

Re: case insensitive "order by" [message #496290 is a reply to message #496212]

Mon, 09 November 2009 15:51

James Sutherland

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

Report message to a moderator

Re: case insensitive "order by" [message #496444 is a reply to message #496290]

Tue, 10 November 2009 06:52

Ashish

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

Report message to a moderator