Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Dynamic input tool -Different sql query method?

BosKev
8 - Asteroid

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.

 

BosKev_0-1619536010042.png

BosKev_1-1619536054935.png

 

 

10 REPLIES 10
AbhilashR
15 - Aurora
15 - Aurora

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

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.

 

BosKev_0-1619540900733.png

 

BosKev_1-1619541003497.png

 

 

AbhilashR
15 - Aurora
15 - Aurora

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

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

 

BosKev_1-1619543658822.png

 

 

Error:

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

 

BosKev_0-1619543551281.png

 

AbhilashR
15 - Aurora
15 - Aurora

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

@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')

 

 

BosKev_1-1619545924675.png

 

 

AbhilashR
15 - Aurora
15 - Aurora

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

 

AbhilashR_0-1619549980974.png

 

BosKev
8 - Asteroid

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
15 - Aurora
15 - Aurora

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

Labels