Dynamic input tool -Different sql query method?

Hey All,

I'm running a simple setup currently in Alteryx where I feed in a list of IDs and query a view in a database for results.

I used "modify SQL query" option to replace

where ID = 'xxxx' with a list of IDs from another input, this works well for small number of IDs...say less than 100 but if I'm to query a large list with thousands of IDs it would take forever as the dynamic tool is query each individual ID separately.

Is there a better solution? Thanks.

Help

Database Connection

Accepted answers

AbhilashR

@BosKev- judging by your screenshot, I am guessing you are concatenating the strings using the summarize tool and then using the Group Replacement Value feature in the Dynamic Input tool. That is probably causing the additional single quotes. If yes, avoid the original summarize approach I mentioned and just using the ID list and pass it to the dynamic input tool. The setup and config in the screenshot are what I am suggesting. It works on my machine:

All comments

AbhilashR

Hi @BosKev, you could probably try concatenating the ID"s using the Summarize tool, and use an IN function in your SQL to lookup multiple values at one go. Let us assume your SQL is something like the following:

SELECT COLUMN1, COLUMN2 FROM TABLE1

WHERE COLUMN2 IN ('id1','id2')

Our configuration would aim to replace the text in blue with the concatenated ID's from upstream.

Attached is a baseline workflow built for a different use case but uses the same concept, where I concatenate IDs in groups of 1000's and pass them to a batch macro to read 1000 ids at a time.

I hope this helps.

OracleRead.yxzp

BosKev

Hi @AbhilashR,

Thanks for the possible solution. I think I just need to convert the list of IDs into the format that acceptable by sql?

As you have done below, then use the Group Replacement function in dynamic input? Or do I need to set the dynamic input within the batch macro? I don't understand the difference.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703