This works on my DB2 (version 9.7 on Linux/Unix/Windows) by using this syntax: SELECT * FROM PLAYERS WHERE (First_Id, Second_Id) IN (VALUES (1,1), (1,2), (1,3)) This syntax won’t work on DB2 on the Mainframe (at least in version 9.1) because you can’t substitute a sub-select with a VALUES expression. This syntax will work: SELECT * … Read more
Pass parameter value like this – ‘AA,BB,CC’. Then, it is enough to use FIND_IN_SET function – SELECT product_id, product_price FROM product WHERE FIND_IN_SET(product_type, param);
This is an example where I use the table variable to list multiple values in an IN clause. The obvious reason is to be able to change the list of values only one place in a long procedure. To make it even more dynamic and alowing user input, I suggest declaring a varchar variable for … Read more
from AUTOS a where a.model in (select m.model from MODELS m) or Query query1 = session.createQuery(“select s.id from Salary s where s.salary < 50000 AND s.salary > 49980”); Query query2 = session.createQuery(“from Employee e where e.id in (:ids)”).setParameterList(“ids”, query1.list()); query2.list();
There’s indeed no straightforward way to do this in JDBC. Some JDBC drivers seem to support PreparedStatement#setArray() on the IN clause. I am only not sure which ones that are. You could just use a helper method with String#join() and Collections#nCopies() to generate the placeholders for IN clause and another helper method to set all … Read more
The above accepted answer is not correct and led me off track for many days !! JPA and Hibernate both accept collections in native query using Query. You just need to do String nativeQuery = “Select * from A where name in :names”; //use (:names) for older versions of hibernate Query q = em.createNativeQuery(nativeQuery); q.setParameter(“names”, … Read more
From the manual: The number of values in the IN list is only limited by the max_allowed_packet value.
You can’t use the IN clause like that. It compiles to a single string in your IN clause. But an IN clause needs separate values. WHERE id_campo not in (@idcamposexcluidos) compiles to WHERE id_campo not in (‘817,803,495’) but it should be WHERE id_campo not in (‘817′,’803′,’495’) To overcome this either use dynamic SQL or in … Read more
If I have an application where I’m building the IN list dynamically, and it might end up empty, what I sometimes do is initialize the list with an impossible value and add to that. E.g. if it’s a list of usernames, I’ll start with an empty string, since that’s not a possible username. If it’s … Read more
PDO is not good with such things. You need to create a string with placeholders dynamically and insert it into the query, while binding array values the usual way. With positional placeholders it would be like this: $in = str_repeat(‘?,’, count($in_array) – 1) . ‘?’; $sql = “SELECT * FROM my_table WHERE my_value IN ($in)”; … Read more