Bit confused by dynamic input variables to in-db tools

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,

Developer

Tips and Tricks

Help

Dynamic Processing

Accepted answers

apathetichell

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.

All comments

apathetichell

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.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335