Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.

modify sql based on user prompt

5 - Atom

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"?

12 - Quasar
12 - Quasar

Hi @bt275567 ,


I have an example of how you could build this. 


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.

17 - Castor
17 - Castor

Hi @bt275567 


Here's an easy way to start



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




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.