Run multiple queries in different DBs

[ivoiculescu2020]

Hello team,

I need your help with the following situation:

For a team project, I need to run 2 queries:

- the first query is run in TOAD and will display a fairly small list of clinical studies and their depot locations

- the 2nd query is run in Microsoft SQL Server. Normally, this query is run manually for each of the clinical studies and each depot location that appear as results for the 1st query. I tried running this 2nd query in the whole DB and then use Alteryx to filter only for the clinical studies and locations displayed on 1st query; however, I was told this query blocks the DB and so I should stop running it. 

How can I configure the Alteryx workflow to make the 2nd query run by automatically "selecting" the results from the 1st query instead of running it in the entire DB and then use the filters?

I tried configuring the MSS connection to RO mode outside of Alteryx but I don't have admin rights on my computer so it can't be done. Is there another way to use the MSS connection without blocking the entire DB?

Thank you!

Kind regards,

Ioana

[Warcry]

One way is to create a macro that takes your output from the results from TOAD. Underneath the hood in the macro you simply replace that in the query. If you need an example I can provide it. 

[ivoiculescu2020]

@Warcry 

Hello,

thank you for your prompt reply.

yes, if you could send me an example and/or a screenshot, I would really appreciate it.

Kind regards,

Ioana

[Warcry]

The first image show the first results and getting prepped to run 1 query for your next results versus individual queries. The second image shows how to concat the ID or whatever you are using to search within the table. The final would be the macro underneath the hood. At the end of the File value in the Action tool, you'll find the query. You'll remove everything except the piece you want to update. In this case the 123. This way the concatenated results are inserted at runtime and then executes your query.  

Let me know if you need any clarification or still need an example. 

[OllieClarke · Accepted solution]

Hi @ivoiculescu2020 

You can use a dynamic input tool to easily update a where clause to filter to the previously output values.
I'd recommend looking at the help page here:

https://help.alteryx.com/current/en/designer/tools/developer/dynamic-input-tool.html
and also at the sample workflow in alteryx to see how to configure the tool

Ollie