Change Entire File Path on Dynamic input to access different DB Schemas

[phoebe_kelley]

I am trying to run the same query on different schemas of the same db. I have each schema set up in my data connections, but I can't get my query to work in the dynamic input, it says " Dynamic Input (3) Unable to translate alias aka:XXX" Is there a syntax I am missing? To create the query string I am concatenating the file path with a dynamic schema name: 'aka:DB Name '+ [SCHEMA] + ' (SELECT * from tableX)'

I tried adding in some pipes, but that didn't seem to work either. Any help would be greatly appreciated!

I have done this before using an ODBC connection, but not with a saved data connection (with the "aka").

[christine_assaad]

Hi @phoebe_kelley 

What do you have under "Input Data Source Template" of Dynamic input? This should have a simple query. See below:

Then you can use Dynamic input "Modify SQL Query" to replace the template query with whatever comes from an upstream tool.

Below I chose to replace the template query with the value coming from the upstream formula that has another query

I'm attaching the workflow in case it can be of help. Cheers!

[phoebe_kelley]

hey @christine_assaad I have to also change the connection string, so I can't just change the query.

[apathetichell]

Dynamic Connect In-DB allows you to change Connection strings and Queries - for multiple uses you will need to use a batch macro. Note - you can also edit the Dynamic Input connection string via batch macro - but I personally like the Dynamic Connect In-DB method.

[phoebe_kelley]

hm, I was hoping to avoid using a macro. I have definitely used this logic before, maybe I have to reference the ODBC connection directly instead of the "aka" connection string. The dynamic in-db won't work since I have multiple records (one for each schema) going into the tool.