Search code examples
spring-data-jpaoracle-sqldeveloper

SELECT query using collate binary_ci with arg mapped to multiple params


I am trying to create a spring data jpa custom query that takes 2 args and uses collate binary_ci. One arg is compared to a string using '=', the other is compared to a string using LIKE. Example that works without collate binary_ci:

SELECT * FROM MYTABLE WHERE ID = :id AND ((MODEL LIKE %:in%) OR (DESCR LIKE %:in%)) ORDER BY ...

The LIKE arg in is mapped to multiple parameters. I cannot get this query to work. I have tried multiple things, but in the end, my attempt to include collate binary_ci is the issue. Here is what I've tried:

    WHERE ID = :id AND ((MODEL LIKE %:in%) OR (DESCR LIKE %:in%)) collate binary_ci ORDER BY ...
    WHERE ID = :id AND (MODEL LIKE %:in% OR DESCR LIKE %:in%) collate binary_ci ORDER BY ...
    WHERE ID = :id AND ((MODEL LIKE %:in%) collate binary_ci OR (DESCR LIKE %:in%) collate binary_ci) ORDER BY

Running these queries gets me either Could not locate named parameter [in], expecting one of [in%, id] or sql statement was not ended properly or missing right parentheses

How can I make this work?

Version: Spring-Boot: (v2.4.3)

Here are version values from my sqldeveloper:

org.openide.specification.version   6.2
org.osgi.framework.os.version   10.0.0
org.osgi.framework.version  1.7.0
os.version  10.0
osgi.framework.version  3.9.1.v20140110-1610

Solution

  • Figured it out:

    WHERE ID = :id AND ((MODEL LIKE %:in% collate binary_ci) OR (DESCR LIKE %:in% collate binary_ci)) ORDER BY...