Updating SQL query based on user input

Question

I have a sql query with period_name = 'SEP-24' in where clause I want to replace 'SEP-24' with user provided period like 'OCT-24', 'DEC-24'. I tried using using action tool, text box to update the value in sql query. I selected the whole query and selected "Replace a specific string" then mentioned part of query I want to replace by user input but I am not able to change the query dynamically. Can anyone please help? I tried mentioning the value that need to be changed in the query in both way 1) SEP-24 2) 'SEP-24'. I am attaching image of my Alteryx wf for reference. Thank you.

WF diagram.PNG

Action tool config.PNG

apathetichell · Answer

@srk0609  --- remove the quotes in your value in replace a specific string. @davidskaife is 100% correct.

having said that--- this is not a great design --- I'd prefer dynamic input in-db.

short explanation --- select * from * where date='Sep-24' --- user puts in Dec-24 --- your new query because you replaced the quotes is select * from * where date=Dec-24 --- and that's an error not a string.

abacon · Answer

@srk0609 Would passing the filter value into a text input tool, then using the dynamic input tool to update the where clause of your sql query work? I believe that would be the easiest option.

You would need a Text input tool with a field that gets replaced using the Text Box/Action piece you have, a dynamic input tool setup exactly like your regular input data tool, but use the modify SQL query option, add a 'Replace a Specific String' option and replace the value you want with the field from the Text input tool. This would run the query but take the filters from text input and replace the where clause.

Bacon

davidskaife · Answer

Hi @srk0609

You need to use the Query under FormatSpecificOptions, like so. I think you also need to remove the quotations: