Case indifferent query

[Daryl Stultz <da...@6degrees.com>]

Hello,

This is probably a general JPA questions rather than OpenJPA, perhaps you'll
permit:

I can do this in SQL:

select * from users where lower(userCode) in ('ab', 'cd');

to do a case indifferent (insensitive) match, assuming 'ab' and 'cd' already
lowercase. The following fails in JPQL:

select o from User as o where lower(o.userCode) in ('ab', 'cd')

(Encountered "lower ( o . userCode ) in" at character ...)

Can I reform this somehow to do a case insensitive "IN" expression. (I have
literal values here in the list, but I'm using a Set<String> in the real
query).

Thanks.


--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com

Re: Case indifferent query

[Daryl Stultz <da...@opentempo.com>]

On Mon, Jun 21, 2010 at 7:30 PM, catalina wei <ca...@gmail.com>wrote:

> Daryl,
> Yes, you can use IN expression and collection valued parameter in JPQL.
> In JPA2 spec , JPQL syntax allows collection valued parameter in IN
>

Thanks, Catalina, unfortunately I'm on OpenJPA 1.2.

-- 
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com

Re: Case indifferent query

[catalina wei <ca...@gmail.com>]

Daryl,
Yes, you can use IN expression and collection valued parameter in JPQL.
In JPA2 spec , JPQL syntax allows collection valued parameter in IN
Expression.
Here is an example:
        Collection params = new ArrayList(2);
        params.add("linux");
        params.add("pc");
        query = "select u from CompUser u where lower(u.computerName) in
?1";
        rs = em.createQuery(query).setParameter(1, params).getResultList();

The SQL where clause looks like this:
    for Derby or DB2:
        WHERE (LOWER(CAST(t0.compName AS VARCHAR(1000))) IN (?, ?))
[params=?, ?]
    for Oracle:
         WHERE (LOWER(t0.compName) IN (?, ?))

Catalina Wei

On Thu, Jun 17, 2010 at 5:13 AM, Daryl Stultz <da...@opentempo.com>wrote:

> On Thu, Jun 17, 2010 at 7:58 AM, gilbertoca <gi...@gmail.com> wrote:
>
> >
> > I think this[1] reference can help.
> >
> > The examples for these functions all use "=" as the operator. I need
> "IN".
> I don't think there is any problem with my usage of lower() as this works:
>
> select o from User as o where
> lower(o.userCode) = 'ab' or lower(o.userCode) = 'cd'
>
> Do I have to iterate over my collection and build this up with OR or is
> there a way to do this with IN?
>
> --
> Daryl Stultz
> _____________________________________
> 6 Degrees Software and Consulting, Inc.
> http://www.6degrees.com
> http://www.opentempo.com
> mailto:daryl.stultz@opentempo.com
>

Re: Case indifferent query

[Daryl Stultz <da...@opentempo.com>]

On Thu, Jun 17, 2010 at 7:58 AM, gilbertoca <gi...@gmail.com> wrote:

>
> I think this[1] reference can help.
>
> The examples for these functions all use "=" as the operator. I need "IN".
I don't think there is any problem with my usage of lower() as this works:

select o from User as o where
lower(o.userCode) = 'ab' or lower(o.userCode) = 'cd'

Do I have to iterate over my collection and build this up with OR or is
there a way to do this with IN?

-- 
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com

Re: Case indifferent query

[gilbertoca <gi...@gmail.com>]

I think this[1] reference can help.

Gilberto

[1]http://openjpa.apache.org/builds/2.0.0/apache-openjpa-2.0.0/docs/manual/manual.html#jpa_overview_query_functions
-- 
View this message in context: http://openjpa.208410.n2.nabble.com/Case-indifferent-query-tp5187921p5190619.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.