SQL Server exception for JPQL. Avoiding null filter-CodePudding

Hi I am trying to execute the following lines of code:

@Transactional
    @Query(value = "select a from ItemAdditionalInfo a where (coalesce(:itemNbrs) is null or a.itemNbr in (:itemNbrs)) and (coalesce(:deptNbr) is null or a.deptNbr in (:deptNbr)) and a.tenantId=(:tenantId)")
    List<ItemAdditionalInfo> paramTest(@Param("itemNbrs")List<Integer> itemNbrs,@Param("deptNbr")List<Integer> deptNbr,@Param("tenantId") String tenantId);

Objective is to avoid parameters which are null.

I am getting the following exception

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.

CodePudding user response：

I have figured out. It solves for me:

@Transactional
    @Query(value = "select a from ItemAdditionalInfo a where (coalesce(:itemNbrs,null) is null or a.itemNbr in (:itemNbrs)) and (coalesce(:deptNbr,null) is null or a.deptNbr in (:deptNbr)) and a.tenantId=(:tenantId)")
    List<ItemAdditionalInfo> paramTest(@Param("itemNbrs")List<Integer> itemNbrs,@Param("deptNbr")List<Integer> deptNbr,@Param("tenantId") String tenantId);