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.
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 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 Parameterized Database Query 2:870 Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword ‘where’.
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…