Parameterized Database Query


The Parameterized Database Query looks like a useful way of constructing queries.

What type of node does it expect in front of Port 1? I tried a Database Connector node but get this error:

Incompatible port spec at port 1, expected: DatabasePortObjectSpec, actual: DatabaseConnectionPortObjectSpec

What type of node works?

Are there any example workflows?



the first port is static data for example, Excel, csv, text files; the parameterized node is used generally is for extract information about values in specific columns of tables

the second port is the conection of database

Hi Mitch,

the Parameterized Database Query node allows you to execute a single query with varying parameters. The node expects a dark red database connection as input. These are the database connections that in addition to the connection information also contain a SQL statement that define the data to work with. The Parameterized Database Query node replaces the #TABLE# placeholder with the SQL statement of the input port. Attached you can find a simple example workflow with the node.




Thank you very much, Tobias!

That example workflow explains how to use the Parameterized Database Query.



Thanks for this example. It was very helpful. Nevertheless, I cannot manage to achieve what I need: looping on the table partition name to retrieve only a limited set of data from those partitions. It seems KNIME does not recognize the partition keyword in the Parameterized DB Query Reader node.

I get the following warning and cannot execute the node.

How can I proceed?

Thanks in advance.

a propper query in the parameterized DB Query Node based on the example wf above goes like this:
SELECT * FROM #table# where “Universe_1_0” > 0.2 and “Cluster Membership” like ‘Cluster_0’

it is a bit confusing for string parametrisation that the columns are writen in " but if you choose ´ for the criteria it works…

Hi all,

I am stuck on a similar nuance with the Parameterized Database Query node. I can’t get it to work using $," or ’ in any fashion - I tried to use the example and it seems to work fine in that. Any tips for how to make the following work? I want to filter client_Key from the database using an table creator where input=18 and both are integers:


Error Message:
ERROR Parameterized Database Query 2:870 Execute failed: Incorrect syntax near the keyword ‘where’.

Thanks for your help!

You need to specify real table name. Also, get read of “” in your query.

1 Like

You’re right - thanks! I needed to replace the #table# with the actual table name including the string starting from the server i.e.
SELECT * FROM [COMPANY_EDW].[Fact].[WorkOrders] where “Client_Key” = $input$

Any suggestions for making this work faster? In some cases my parameter list is thousands of rows long…

1 Like

There is some difference in functionality but Database Pooping node may work for you.