HOW TO DYNAMICALLY CHANGE WHERE SQL CLAUSE USING IN-DB DATABASE TOOL

[vibes360]

I have a workflow where I am connecting to a SQL database that is very large with over 10 million records. I want to use the result of a Summarize tool that has about 500 Codes to automatically flow into the where clause of the original SQL statement that will be coming from the IN-DB Tool. Please can someone show me how to do this in Alteryx as I don't know the combination of IN-DB tools to do this logic

I already tried this with the Dynamic Input tool but it takes forever for the workflow to run so I can't use it.

[Hamder83]

have you tried to use the oledb driver to do it when dynamic input?

[BRRLL99]

You can save the SQL data in YXDB format 
and do your calculations accordingly.

[danilang]

Hi @vibes360 

Your question is equivalent to joining your main table to a "Code" table on the code field and only returning the matching records, where the records in the Code table are dynamic.  The In-DB config of this is  

The Code Table text input represents the codes that you want to filter for, 1 one per line.  Use a Data Stream In tool to have the process create a temporary table in your database.  Join this table to your Main table on your Code field.  The Join tool will act as a dynamic filter.  

Dan

[vibes360]

@danilang Thank you, this is what I want to do exactly. Please can you send me the workflow you have used in the picture. You can use dummy queries so you don't show real data

[danilang · Accepted solution]

Here you go @vibes360 

The key point is to use the same data connection for the Connect In-db and Data Stream In tools

Dan 

[vibes360]

@danilang Thank you. That works 

[oliverchadwick]

Is there a workaround to this if you don't have permission to create temporary tables in the database?..