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.
I'm a new user, trying to figure out how to prompt a user for a month, then use that input to modify an SQL where clause. Should be simple but I can't figure it out. Could someone please spell it out for me, something a bit more detailed than "use a dynamic input tool"?
I used a text input tool to allow me to enter start dates and end dates.
Then connected to dynamic input tool. Select this option:
Select the specific where clause you want to modify:
Then under add choose the option that says SQL Update WHERE Clause choose Add.
This will allow you to pick specific WHERE clauses where you can modify the text in the SQL. In the instance below I'm replacing the "dummy text" 20APR2009 with the value in field 1
Since you want to prompt a user - will you be building it out as an app? If so that adds a little bit of complexity as you will need to choose what interface tools will allow the user to enter values. Or if going to share as a workflow you should be good to go!
I held back a bit on the screenshots for confidentiality reasons etc....but these should help.
The text input just provides a dummy field to get the process started. The formula tool contains the entire SQL statement. The drop down contains a list of month in this format
Jan:1 Feb:2 Mar:3 Apr:4 May:5
When the user selects a value, say May, the value 5 is passed to the action tool, which replaces the "1" in "t.month=1" with 5. This new string is passed to the Dynamic Input tool which is set to replace the entire template SQL string with the new one
You'll have to add in your own connection and template SQL string to be replaced, since you won't be able to access any of the data sources that I could set up.