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

Question

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").

phoebe_kelley · Answer

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.

apathetichell · Answer

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 · Answer

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