GETTING STARTED | DATABASE OPERATIONS | KNIME ANALYTICS PLATFORM

KNIME Database SQL Filter using the IN Operator

Flow variables and a pinch of ingenuity can take you a long way!

As first published on Creative Data

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 node but this cannot be used with DB connections. The other standard method would be to use the DB Row Filter node 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 a DB Query node as a flow variable and then used as part of an “IN” filter in an SQL statement.

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 the GroupBy node to concatenate the items into a single row using ‘,’ as a delimiter.

Note. The delimiter is not just a comma but it also has single quote marks.

Do NOT group the items.

Grouping.

Now, we have a long list of our items looing something like this:

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

Note that we are missing the opening and closing quotes at the moment. To close the quotes, use the String Manipulation node with this configuration.

Close the filter string with quotes.

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

Final Filter string.

To add this to the DB Query node, first generate a flow variable from the table row using the Table Row to Variable node.

Generate variable.

To add the flow variable to the DB query, connect the Table Row to Variable node to the flow variable port of 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 like the one shown below 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 workflow looks like this when put together.

Full DB Filter flow using IN Operator.

Easier done than said, uh? Happy KNIME-ing!