JPA Query clause IN / Invalid relational operator-CodePudding

How to include the IN clause in the Query if the parameter is not null?

If I try to put the ":ramos is null" it gives an error

@Query(value = "SELECT r FROM ParceiroNegocio r "  
        " WHERE (:razaoSocial is null or UPPER(r.razaoSocial) LIKE CONCAT('%',UPPER(:razaoSocial),'%')) "  
        "   AND (:nomeFantasia is null or UPPER(r.nomeFantasia) LIKE CONCAT('%',UPPER(:nomeFantasia),'%')) "  
        "   AND (:cnpj is null or r.cnpj =:cnpj) "  
        "   AND ((:ramos) is null or r.ramo IN (:ramos))")
Page<ParceiroNegocio> findByCnpjNomeFantasiaRazaoSocialRamoWithPagination(
        @Param("razaoSocial") String razaoSocial,
        @Param("nomeFantasia") String nomeFantasia,
        @Param("cnpj") String cnpj,
        @Param("ramos") List<Long> ramos,
        Pageable pageable);

}

Error:

SqlExceptionHelperORA-00920: invalid relational operator

CodePudding user response：

COALESCE helped me! :)

AND (COALESCE(:ramos) is null or r.ramo IN (:ramos))