This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
If the replace a specific string doesn't work in your dynamic input tool, my workaround for most things similar to this is to just add a second table with the text input tool with all of your query parameters, then append the data to your initial query and run it through a formula tool with the formula [Query]+" "+[Clauses] and then pass that formula field through as your query in the dynamic input tool. Using the text input tool keeps you from having to add multiple formulas or tools to your workflow, just structure the input as such:
then when you append the data to your initial query and pass through the formula tool it does the same work as adding a bunch of text inputs, conditional formulas, or action tools.
David, I am working through your second example file. I am confused (as usual lol). After the formula tool, how do I connect to a dynamic input tool to query my db2 database. Will it not work this way?
If you're going with the route of building up the WHERE clause in the Formula tool as @DavidP suggested, then in your Dynamic Input tool you'll have to use replace specific string on the entire WHERE clause.
Also, if you're using this route then there's no need to write out all the parameters in your template SQL in the Dynamic Input tool. You can specify something short in the WHERE clause (e.g. "... WHERE 1=1") since it will all be replaced anyways.
I would do the following to troubleshoot the 1st error.
In a new workflow, connect to your database with an Input Data tool and write the entire SQL statement in the SQL editor and get it to work. Then copy the entire sql query into the formula tool as a new field and use that field as the field in your dynamic input tool.
If this doesn't work, something is wrong in the dynamic input tool configuration and if it does work, something is wrong with the sql query you construct in [WHERE CLAUSE]. Compare [WHERE CLAUSE] with the SQL query you wrote and see if there is a difference. It looks like the problem is with the syntax of your SQL query.