KNIME Database SQL Filter using the IN Operator

Sometimes using KNIME it would be nice to create a DB row filter based on a list of items. When not using DB tools you can use the reference row filter but this cannot be used with DB connections. The other standard method would be to use the DB Row Filter but this only supports a few operators such as = and like.

DB Row Filter options

If you want to filter by a long list that doesn’t follow a pattern it’s a lot of work to create the filter, plus it’s not dynamic.

Create a Dynamic “IN” Filter

My solution is to create a dynamic string that can be injected into the SQL using a variable and then used as part of an “IN” filter.

First create a list of the items you wish to use in the filter. In this case I’m using a static list but they could also come from a dynamically generated list.

Filter list

Next use a Group By node to concatenate the items into a single row using ‘,’ as a delimiter (note this is not just a comma but also the single quote marks). Do NOT group the items.

Grouping

Now we have along list of our items looing something like this, not we are missing the opening and closing quotes at the moment.

item1','item2','item3','item4','item5

To close the quotes use a String Manipulation node with this configuration.

Our list is now in the correct format to inject into the SQL.

Filter string

To add this to a DB Query first generate a variable from the table row.

Generate variable

To add to the DB query connect this node to the DB Query node and create an SQL statement using the IN operator as shown below.

SQL IN Operator

This will generate an SQL query something like this where the list we created is inserted into the SQL.

SELECT * FROM (SELECT * FROM "dbo"."Sales Header"
) AS "table" where "No_" in ('SO12345','SO12356','SO45633','SO95959','SO38475','SO84737','SO94836','SO94847','SO77363','SO99884')

The full flow looks like this when put together.

Full DB Filter flow using IN Operator

Leave a comment

Your email address will not be published.