CODEWITHSUNDEEP
javamysqljpa
afrikaan
afrikaan

Reputation: 425

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?

Upvotes: 2

Views: 1517

Answers (2)

afrikaan
afrikaan

Reputation: 425

As far as i understood there is no isEmpty or length in jpa queries if your parameter is an array like in this case. @SOlsson was right (1,2,4 is null or... is not valid Sql so i decided to add an extra parameter to my query in order to check if its null or not so my last query like:

        ...
            "((:hasEquipmentNumbers) is null or e.tagNo in (:equipmentTagNumbers)) and" 
        ...

:hasEquipmentNumbers is a Boolean which i can assign null so if its null then nobody hurts and if its not i can run my IN clause with no trouble.

PS:I marked his answer cuz, it seemingly like an answer. But i implemented like i explain.

Upvotes: 2

user8654079
user8654079

Reputation: 

((: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.

Upvotes: 1

Related Questions