Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

vibes360
8 - Asteroid

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.

7 REPLIES 7
Hamder83
11 - Bolide

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

BRRLL99
11 - Bolide

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

danilang
19 - Altair
19 - Altair

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  

danilang_1-1687193016060.png

 

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
8 - Asteroid

@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
19 - Altair
19 - Altair

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
8 - Asteroid

@danilang Thank you. That works 

oliverchadwick
5 - Atom

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

Labels
Top Solution Authors