• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

[JPA/TOPLINK] is the function "lower" supported in "order by" clause? if not, how....

 
Edward He
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In EJB-QL
I can use lower() or upper() in where clause.
But there is always a parse exception thrown when i tried to use it in main clause or order by clause.

works:
select s from Student s where lower(s.name) like 'm%'

exception thrown:
select s from Student s where s.name like 'm%' order by lower(s.name)

OR
----------------------------------------------------------------------------Why i am asking this is that the resultset returned from database is not alphabetical sorted but ascii sorted, which means any characters with upper case is always prior to the ones with lower case. i.e. Zac is prior to billy in ascending order which is not acceptable.

If EJB-QL doesn't support using lower() in order by clause, do I have any other options to avoid this problem?

BTW, it is the Oracle 10g we are using as DB
TOPLINK as the ORM framework

many thanks,
Edward
[ November 29, 2007: Message edited by: Edward He ]
 
James Sutherland
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My guess is that the JPQL grammar does not allow this. However TopLink still should. I logged this issue internally in Oracle's bug database, feel free to log a bug on Glassfish if using TopLink Essentials, or Eclipse if using EclipseLink, or contact Oracle technical support.

As a work around you could use a TopLink Expression query, or use a native JPA query, or sort in memory.
 
Edward He
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot James,
I will take a look at Toplink expression query, i guess it should do the trick
 
Edward He
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi James,
is there a public access to the toplink bug tracking system?
There are few people in my organization who also interested in the defect you reported.
 
James Sutherland
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The TopLink Essential bug tracking system is public, and the EclipseLink bug tracking system is public. You can access the Oracle TopLink bug tracking system through Oracle support. You can see some of the published bugs through Oracle meta-link, but I did not publish the bug.
 
Edward He
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We found another solution for this issue.
by changing the oracle system setting parameter "nls_sort"

change it to nls_sort = BINARY_AI will solve the puzzle
 
Mike Keith
author
Ranch Hand
Posts: 304
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
BTW, James is correct that JPA does not support using string functions in the ORDER BY clause of JPQL queries. This is outside the realm of the spec.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic