Switches with DB Query Reader

I’m working with a workflow and am curious if KNIME has the capability to execute different DB Query Reader nodes based on a string variable. In the pictured workflow, I’m working within a component node that prompts users for a State variable and year variable. I’m attempting to execute a specific query based on the user inputted state. For example, only the middle Query Reader node would be executed if the input was “MI” and the bottom Query if it was “MA”. I’m thinking that an IF switch might be a potential solution, but it only accepts table input. My only other thought would be to use a CASE Switch, but I’m unsure how that would be accomplished. Thanks in advance for any help or suggestions.
Knime Switch
CASE Switch

Hi @JGebhard ,

Using case switch, your component flow could look something like this:

The Rule Engine (variable) would define an Integer variable that is assigned 0,1 or 2 based on user input.
The case switch then passes flow to the query reader according to that variable

A case Switch End is added with 3 data input ports to return the results. I would presume from this that the resultant table structures from each of the DB Query Readers is the same!

There are alternatives to this. Depending on how much difference there is in the SQL for each query, you could have a single DB Query Reader and simply pass in parameters, or build entire sections of SQL in flow variables using such nodes as String Manipulation (Variable) or Variable Expressions nodes, but this is a reasonably simple flow that should be adaptable for your needs.

2 Likes

Hi @JGebhard

I assume you want to switch the output ports based on the string input right?

Add input/output type as DB session :

Next, add a 3rd output port so you have 3 output ports in total

Now you should be able to create the following :

Specify rules in the rule engine (variable) node. Port indices are read from top to bottom where 0 is the top port:

The above means essentially “go through the top port if the input is MI , through the middle if the input is “MA” & otherwise, by default go through the bottom port i.e. port 2 .

For some reason the port index variable is only accepted as a string, therefore the variable must be of string format (hence the string manipulation node)

Go to the case switch node & enter variable settings.

And now you should be good to go i.e. when you change the string input accordingly the data should flow into the correct port.

I used the following dataset to play around with the settings:

conditional_db_query.knwf (23.3 KB)

You can do a file swap in the sqlite connector node or connect to your own database & write respective db queries & the rest of the workflow should work as intended.

Hope this helps!

Best,
Adam

3 Likes

Thank you very much!

Thank you very much. Your step-by-step instructions were extremely helpful

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.