[JPQL] IN clause

[Fabian Angy]

Hi!

I've got a problem with my "IN" clause.
If i do "select DISTINCT a from A a, IN(a.errors) ae where ae.errorCode IN (:errorCode)"
And if I have one value in errorCode (setErrorCode("xxx") it works but if I have more than one value (setErrorCode("xxx, yyy")) it doesn't work!
In fact, when i have more than one value, I use a method which returns each value with " ' ' ", by example i'll have "select DISTINCT a from A a, IN(a.errors) ae where ae.errorCode IN ('xxx', 'yyy')" and not "IN ('xxx, yyy')".

Someone knows why it doesn't work?

Thanks in advance!

[James Sutherland]

To bind an collection parameter you need to remove the brackets,

i.e.
select DISTINCT a from A a join a.errors ae where ae.errorCode IN :errorCode

otherwise use,

select DISTINCT a from A a join a.errors ae where ae.errorCode IN (:errorCode1, :errorCod2, :errorCode3)

[Fabian Angy]

My problem is that all of values are in only one variable :/ I can't do :errorCode1, :errorCode2, ... and without brackets, it doesn't work, i've got an error!

[James Sutherland]

What is the error? What JPA provider and version are you using?

[Fabian Angy]

java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager
...
Exception Description: Syntax error parsing the query 
[select DISTINCT a from A a join a.errors ae where ae.errorCode IN :errorCode ], line 1, column 97: syntax error at [:errorCode].
Internal Exception: MismatchedTokenException(88!=69)
...

with EclipseLink 1.0.2

[James Sutherland]

This was not supported in JPA 1.0, but is in JPA 2.0. You should upgrade to the latest EclipseLink 2.3. I think it was fixed in 1.1, so you need at least 1.1.

[Fabian Angy]

Oh ok!

But I don't understand why it works when I put myself the values (by example: IN ('000', '111')) but not when I put a parameter (IN (:errorCode)) :/ If it supports the values hardcoded, why not the parameter?