kyur kyur - 3 years ago 151
Java Question

using in clause inside jpa query gives Operand should contain 1 column(s) error

i'm trying to use

in
clause inside a jpa query but it gives
Operand should contain 1 column(s)
error.

Here is my query:

@Query(value = "select e from Table e where " +
"((:plantId is null and :unitId is null and :functionalLocationId is null) or" +
" (:functionalLocationId is not null and e.functionalLocation.id in (select f.id from FunctionalLocation f where f.id = :functionalLocationId)) or" +
" (:unitId is not null and :functionalLocationId is null and e.functionalLocation.unit.id in (select u.id from Unit u where u.id = :unitId)) or" +
" (:plantId is not null and :unitId is null and :functionalLocationId is null and e.functionalLocation.unit.plant.id in (select p.id from Plant p where p.id = :plantId))) and" +
"((:equipmentTagNumbers) is null or e.tagNo in (:equipmentTagNumbers)) and" +
"(:startDate is null or e.lastUpdateDate >= :startDate) and" +
"(:endDate is null or e.lastUpdateDate <= :endDate)" +
"order by e.id desc")


:equipmentTagNumbers
property is a
Lis<String>
and if i send null for it query works as i expected but when i send actual data it gives the error.

any suggestions?

Answer Source
((:equipmentTagNumbers) is null or...

becomes

(1,2,4 is null or...

which is not proper SQL.

Instead, go like this:

@Query(value = "select e from Table e where " +
        "..." +
        (equipmentTagNumbers == null ? "" : "e.tagNo in (:equipmentTagNumbers)) and ") +
        "..." +
        "order by e.id desc")

That way equipmentTagNumbers does not affect the query when it is null.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download