Hi,
Scalar functions can only appear in WHERE clause in JPA 1.0 JPQL syntax.
If you upgrade to OpenJPA 2.x, scalar functions can appear in selections so
you can use 'result variable' to get the ordering results. The following
JPQL would return case-insensitive ordering results of the result variable
- orderField:
"SELECT someObjType, LOWER(somObjType.someFieldName) as orderField" +
" FROM SomeObjType someObjType " +
" WHERE ...." +
"ORDER BY orderField"
If upgrade to OpenJPA 2.x is not an option, the last option is to use
NativeQuery.
Catalina
On Mon, Dec 13, 2010 at 10:25 AM, jasha <[email protected]> wrote:
>
> Hello,
>
> I am using an old version of OpenJPA (1.0, comes with Weblogic 10)
>
> I have some code like:
>
> String queryStr = "SELECT someObjType " +
> " FROM SomeObjType someObjType " +
> " WHERE " +
> ...
> ...(big where clause)
> " order by someObjType.someFieldName";
>
> (someFieldName is a string/varchar)
>
> Now this works fine, but I have to change the ordering to case-insensitive
> ordering by someObjType.someFieldName. When I change the code to order by
> LOWER(someObjType.someFieldName) I get an exception:
> org.apache.openjpa.kernel.jpql.ParseException: Encountered "lower" at
> character 336, but expected: [<IDENTIFIER>].
>
> What is the correct way to do a case-insesitive ordering on a string field?
> There must be some way to do this without writing a native query (which
> would greatly complicate my query in this case)
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Case-insensitive-ORDER-BY-in-JPAQL-tp5831748p5831748.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
