KNIME Database SQL Filter using the IN Operator

GETTING STARTED | DATABASE OPERATIONS | KNIME ANALYTICS PLATFORM

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

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.

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.

Next, use the GroupBy node to concatenate the items into a single row using ‘,’ as a delimiter.

Do NOT group the items.

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

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.

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

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

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.

This will generate an SQL query like the one shown below where the list we created is inserted into the SQL.

The full workflow looks like this when put together.

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

Related posts:

Started on the weekend ie., Saturday morning at 5:26 AM on my Suzuki Access 125 CC towards Chikmagalur. Planned to cover temples on the go before reaching Chikmagalur. The first destination is…

Picture this scenario: you work through your adult life and then you gradually tick things off your bucket list before you turn 50. Wouldn’t that be perfect? However, to turn all your dreams into…

Smart goals are dull, uninspired, mundane, boring, and safe. I see many people using and teaching this technique still and I think it imposes limitations on our dreams. In goal setting, I see such a…