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.
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.
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.
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.
To add this to a DB Query first generate a variable from the table row.
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.
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.
