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

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.

Workflow

Datasets

Developer Tools

Input

Tips and Tricks

Custom Tools

Connectors

Transformation

Dynamic Processing

Join

Database Connection

Accepted answers

danilang

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

Dynamic Where Clause(dummy connection).yxmd

All comments

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

oliverchadwick

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

Quick Links

Unanswered
Community Events
Groups

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335