Dynamic input tool -Different sql query method?

[BosKev]

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.

[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.

[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.

[AbhilashR]

Ah yes, try the Group Replacement value approach. I am a creature of habit and had learned to do this the hard way back when I was starting out and often oversee this default feature. Thanks for the reminder! 

[BosKev]

@AbhilashR , it almost worked but I'm getting weird syntax error like below where there are extra single quote added to the modified query:

input:

Error:

and a.ID in ('''xxxx'',''xxxx'',''xxxx''')  

[AbhilashR]

@BosKev- could I request you to paste the exact error msg you see? If you are using Oracle, it has a limit of 1000 ID's is something to keep in mind. 

[BosKev]

@AbhilashR you are right about the limit, so I tried with only 3 IDs, no error this time but no results either with is incorrect. The IN syntax is wrong when I use modified sql in dynamic input(see my previous post), not sure how to update it.

it should be ---> a.ID  in ('xxxx','xxxx','xxxx')

[AbhilashR · Accepted solution]

@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:

[BosKev]

Thanks, that worked! I originally thought the list need to be in the right format(i.e. in ('a','b','c')) in order to the grouping function to work...but it looks like tool already does that for you.

[AbhilashR]

My response probably also led to the confusion:) Thank you for being patient.