KNIME Documentation

Parameterize a SQL query ​

Use the DB Parameterized Query Reader or DB Looper node to execute SQL queries that take values from an input KNIME table — for example, to run a query once per row, or to filter with a dynamic list of values.

Before you begin ​

Execute a query once per input row with DB Parameterized Query Reader ​

The DB Parameterized Query Reader node loops over an input KNIME table and executes the SQL query once for each row, substituting column values as parameters.

  1. Connect the DB Connection output port to the DB Parameterized Query Reader node.
  2. Connect the KNIME data table with parameter values to the second input port.
  3. Click the node once to open a compact SQL editor, then click the expand icon to open the full configuration.
  4. Write your SQL statement, referencing input columns as parameters using the syntax defined in the node configuration.
  5. Under Output Options, configure how the result is built:
    • Append input columns — adds the input table's columns to the output.
    • Include empty results — keeps a row for input values whose query returns no data.
    • Retain all columns — keeps the union of columns across all query executions.
    • Fail on error — fails the node if a query execution returns an error.
  6. Click Show advanced settings to configure Input Type Mapping, if needed. See Type mapping.
  7. Click Apply to save the configuration, or Apply and Execute to save it and run the node.

Filter with a dynamic list using DB Looper ​

The DB Looper node executes a SQL query restricted by the values in an input KNIME table. It replaces a column placeholder $ in the query with a comma-separated list of values from the table.

Use it for IN queries where the list of values changes at runtime:

sql
SELECT * FROM table
WHERE Col1 IN ($)
  1. Connect the DB Connection output port to the DB Looper node.
  2. Connect the KNIME data table containing the filter values to the second input port.
  3. Click the node once to open a compact SQL editor, then click the expand icon to open the full configuration.
  4. Write your SQL query, using $ as the placeholder for the value list.
  5. Under Loop Settings, set No of values per query to the number of values to substitute per query execution, or tick Read all to substitute every value in a single query.
  6. Configure Aggregate by row, Append input columns, Include empty results, Retain all columns, and Fail on error as needed.
  7. Click Show advanced settings to configure Input Type Mapping, if needed.
  8. Click Apply to save the configuration, or Apply and Execute to save it and run the node.

An example workflow is available on KNIME Hub.

Next steps ​