Bit confused by dynamic input variables to in-db tools

[RBF]

Hi! 

Looking for a starting point really.  Tried googling but getting nowhere.

I have an sql query that pulls data from a database for further transformations in alteryx.  I wrote the sql query in the sql editor.

What I want to be able to do is have the sql query pull variables from other tables before running in the server.

For example, maybe I want to filter on a specific list of customers when I run the query, but that list of customers is not always the same.  That list is maintained in a separate table in a different place (not in same db) and called upon when the query runs.

Any youtube videos, articles, or example workflows, would be greatly appreciated,

[apathetichell · Accepted solution]

put your sql code in a text input tool. use summarize to make sure it's one line - not comma seperated. add an append fields tool with your variables. use formula to change your query to reflect your variables. use formaula to add  a column referencing your db name. use dynamic input in-db to run your query.

[RBF]

oh, haha, my query is 200 lines so that's going to be pretty ugly on one line - but I will try it!

Thank you!

(I'll accept as solution if and when I get it working)

[apathetichell]

Sure - you can do this as 200 lines of text - just use your summarize tool to summarize it to one line... I do that all of the time. For readability you can add /n as a separator -but hopefully you won't need to read your query after concatenating.

[RBF]

Yes!  It took a bit of fiddling about until I understood it properly but I finally got it working!  (actually not that dificult when you know how)

Now i just need to figure out how to protect my query from bad input strings - is there something like an escape function in alteryx?  The input tables for the dynamic variables are out of my immediate control so need to ensure nothing funny happens.