Update Input Parameters in SQL Statement before Passing it to SQL Editor on Input Tool

[yijentu]

Hi,

Currently I have a 1600-line SQL statement that I'm passing into the SQL Editor on Input Data tool to query our Oracle database.

In the SQL statement, there's a input parameter (org code) I need to update before passing it to the SQL Editor. I have to do so for about 50 organization and compile all the output into one database. 

I have a rough idea that I'll have to use batch macro and perhaps dynamic input tool, but I wasn't able to wrap my head around on how to put all the elements together and get it to work. 

Essentially I need to loop through my List of Org Codes and do the following steps:

- Replace {?input_organization_code} in the SQL Statement by Org Codes

- Paste the SQL statement into SQL Editor on the Input Tool

- Run workflow

- Append output for each Org into the same database

Any direction or suggestion would be greatly appreciated. Thank you!

[Claje]

Hi,

I attached a VERY basic example flow (with no regard for proper syntax or connection string) to give you an idea of how to set up the Dynamic Input tool.

You can replace the connection information with yours, and put the query into the Dynamic Input tool (including the {?input_organization_code} statement), and then replace that using Dynamic Input.

Once you know its working (test by putting in like 3 org codes into the text input), then try changing your source to the full thing.

Hope this helps!

[yijentu]

Hi Claje,

Thanks a lot for your prompt reply! I gave it a try but it did not work... Is it because most of my input parameters are not in the where clause?

Below is what part of the SQL query looks like & the error message I got. As you can see, only one of them are placed in the where clause.

I also tried Replace Specific String, but I wasn't able to input anything into the box. Am I missing something? Or is there any other approach I can try? Thank you very much!

[Claje · Accepted solution]

That's really unusual that you couldn't input anything into the text box.  I was just about to recommend "Replace String" given your logic.

I attached a revised copy of my initial workflow with that Replace String built in, to see if that at least works for you.  Try putting in your query/connection into the dynamic input tool in this flow.

If this works, try closing and re-opening alteryx to see if you can customize the "Replace string" clause.  If you still can't, try rebooting, and if that still doesn't work, I'd recommend asking Alteryx support (support@alteryx.com)

[yijentu]

Thanks a lot Claje! I tried rebooting my computer and it worked!