Drop Down update SQL WHERE clause with Input or Dynamic Input

[PeterAP]

Hi

I am trying to use a drop down box to modify the SQL WHERE clause but I'm struggling to get this to work properly.

My WHERE Clauses are as follows and I need three drop downs:

where table.year = '0'
and table.period >= '0'
and table.period <= '0'  

If I use the drop down tool and connect the the input tool, I can only seem to be able to update a specific piece of text, but as you see above, if I used it to replace '0' then it would replace all three in the tool rather than the related one.

However, when I try to use the dynamic input tool, it's asking for an input feed - even though the SQL is in there and doesn't need a separate data feed. So I tried attaching an input tool with the same SQL to the front end, but it doesn't seem to work when I run as analytic app.

Does anyone have any tips?

[echuong1]

I suggest changing the placeholders to something else rather than "0" for all three. From there, you can have a drop-down and action tool to control each.

Alternatively, you can dynamically compile the SQL query with the updated parameters and then use a dynamic input.

[PeterAP]

Thanks for the reply @echuong1 !

I thought about the first one - but I want to be able to run it in both designer mode and analytic app mode - so need it to have some useful defaults.

How would I dynamically compile the SQL - this is what I was trying to do below, but looks like it needs another tool to connect to the dynamic input tool? Any help appreciated!

[echuong1 · Accepted solution]

You'd create the where clause dynamically, and then that would update in the dynamic input tool.

In the example attached, the text input contains the three parameters. I'm using numeric up down and action tools to update each parameter individually. I'm then using a formula tool to dynamically compile the where clause with the three parameters. 

This would then connect to the dynamic input tool. Select the option to replace a specific string, and replace the entire where clause. The where clause compiled in the formula tool would be the replacement.

Hope this helps!

[PeterAP]

Thank you! This worked perfectly!