Multiple Date Variable into DB Query Reader?

[mdaopani]

Hi everyone, just learned how to use KNIME a few days ago and it’s been awesome but I’m currently stuck with a workflow I have. Basically I want to create a workflow queries a SQL database but I want to be able to input date ranges to limit my query in the workflow.

For example in DB Query Reader, my SQL should be something like:

Select *
From SalesTable
Where Date between [Begin Date Variable] and [End Date Variable]

My issue is I can’t seem to figure out how to have multiple Date Inputs into DB Query Reader… Any advice would be appreciated, thanks in advance!

[takbb]

Hi @mdaopani, welcome to the KNIME community.

Take a look at my post on a similar topic here…

SQL filter by a Temporary combined column

Hi @AndrewDeacon , you are already on the right lines. The attached workflow gives one possible example of how a SQL query in a DB Query Reader can utilise the values from flow variables to “pre-limit” the returned data from an H2 sql database. You will have similar constructs in your database, and should hopefully be able to apply something similar. db_pre_select.knwf (26.1 KB)

And also the following link to the hub, although not directly about DB Query Reader might give you some clues

Dynamic SQL and parameter passing – takbb

If you still have more questions or anything isn’t clear, please feel free to ask some more

[mdaopani]

Thanks for that, but unless I’m mistaken with that method there’s no way to solicit an input from the user when the workflow is executed? That’s the key part that I’d like to use from the Date Wizard.

[takbb]

Hi @mdaopani, I had misunderstood what you were asking. I thought you wanted to know how to incorporate multiple variables inside the DB query reader once you had them.

To get multiple data inputs in the first place, simply chain another date&time input node onto the one you already have in your workflow using the flow variable port. Both flow variables will then flow down to the DB query reader where you can use them. I hope I’ve now understood your question and that this makes sense.

[bruno29a]

Hi @mdaopani , you can use the Variable Expressions node to input as many variables as you like, instead of using the Date&Time Input node.

You add the variables in the node like this:

And you can see the output variable of the node - you can see the 2 date variables I created:

And then you can use them in your Query Reader:

I highlighted the Flow Variable List section where you can see the variables. You can double-click on the variable you need, and Knime will add the variable to the SQL Statement for you. You don’t have to know how to write the format of the variable - Knime takes care of it for you. But for your own knowledge, the format for variable is basically enclosed within $${}$$, and the S at the beginning simply denotes that it’s a String variable, hence the variable “begin_date” for example, is written as “$${Sbegin_date}$$”.

Just double click on any of the Flow Variable List if you want to familiarize yourself, you can always delete it from the SQL Statement.

[mdaopani]

@bruno29a This is great! However I want to take it one step further, any way to make the workflow prompt the user to enter in a date in some kind of input form when the workflow is executed?

[mdaopani]

@takbb Thanks, I also tried that but when I do that it just ends up asking me for the end date over and over without asking me to input the begin date…

[bruno29a]

Hi @mdaopani , for any interaction/interface, you would have to use components where you can group multiple inputs in the same window. However, the workflow will prompt the user only on the web portal of the Knime server.

On the back-end, it would not “prompt”. The user would have to open the component and enter the dates.

If you really want real date input as interaction, then you can use whatever you were doing originally, but you can just use 2 Date&Time Input. You just need to connect them to each other, and use different variable names:

Use the proper variable name, and user can choose the date from the pop-up:

Similarly as I mentioned in the previous post, you can see the output variables:

If you want to convert these 2 nodes as a component for interaction, just highlight them, right click, and choose Create Component:

They will become one component:

If you double click on the component, it will give you an interactive window:

As you can see, you can interactively change the date/time.

Of course, it’s not saying much with the default Labels. You can change the labels in the Date&Time Input nodes before converting them to components, or you can go into the component and change them in the 2 nodes.

Simply hold the CTRL key down, and double click on the component, it will allow you to edit the nodes in the component:

Open each of the 2 nodes, and change their respective labels:

One more thing, very important. Variables within components do not follow out by default. If you click on the Component Output node, you will understand:

This is what you will see:

As you can see, the 2 variables are excluded by default. Just move them over to the right, to the inclusion part:

Now, these 2 values will be outputted by the component. And this is how the component interaction looks like after these changes:

Have fun exploring components.

EDIT: I’ve attached my workflow in case you have issues with components: Multiple Date Variable into DB Query Reader.knwf (17.1 KB)

[mdaopani]

@bruno29a Wow this is great, thanks so much for the detailed walkthrough! I’ll give this a try today but it looks like this is exactly what I need!