I want to make SQL query to be entered dynamically in input data tool

[MahanteshS]

PFA screenshot for description of issue

[LordNeilLord]

Hey @MahanteshS

What do you need to change with the SQL?

Neil

[MahanteshS]

I have to input SQL query like "select * from table"...etc...but that query I want to give it in run time through the analytic app feature

[danrh · Accepted solution]

You can do this by using the Interface tools:

1- In an Input Data tool, query the database you're going to be dealing with.  If you're going to be switching connections (i.e. connecting to SQL Server vs Oracle) then this will need to get fancier.  The query you use doesn't need to be anything in particular, this is a placeholder.

2- Drop a Drop Down tool on the canvas, and drag the "Q" output to the Input Data tool.  An Action tool will automatically be added between the two.

3- In the Drop Down, type whatever prompt you want.  I'd also check the "Multiline" option to give yourself more space to enter your new SQL query.

4- In the Action tool, make sure "Update Value" is selected in the Action Type dropdown, and select the "File" attribute in the tree.  You'll notice that at the tail end of this string you have the SQL query you originally entered in the Input Data.

5- Check the "Replace a specific string" option.  Then in the box just below, delete everything except your original query.

The trick at this point is to run the workflow as an Analytic App.  In the toolbar at the top, next to the standard green Run Workflow button you'll notice there's now a Wand icon.  When you added the Interface tools, the workflow was automatically converted to an Analytic app, but in order to run it as such you have to click the wand instead of the run button.  When you click it, drop the query you want to run into the prompt and it should produce what you need.

Good luck!  Hope it helps!