Alteryx Dynamic SQL IN clause for queries exceeding 1000 items *With solution*

[irfytw]

Problem Statement: Need to inject a dynamic list into a SQL IN clause that may exceed the 1000 limit.

Solution:

• I first isolated my column containing the row of values I needed to inject into the SQL IN clause with the column filter node.
• I then appended a new column that would contain a constant value to be used in the group by node.
  
• I then used the group by node to group the data by the single value and then set the aggregate method to concatenate with a comma as the delimiter.
  
  
• After the column is concatenated, I used the String Replacer node to apply a RegEx Replace function to apply the single quotes between each part number. The pattern is ([^.]+) and the Replacement is ‘$1’
  Make sure to toggle the “All occurrences” replacement strategy.
  
  
• I then used the row_to_variable node to create a flow variable from the concatenated and RegEx output.
  
• I then I connected the flow variable into the DB Query Reader node and put the flow variable in my SQL IN clause between the parenthesis.
  
  
• The final step is to add the loop end node to allow the flow to continue past the 1000 item limit.
  Here is the workflow visual.
  

Cheers,
Tyler

[izaychik63]

I would recommend to use

DB Looping — NodePit

node instead of injecting SQL.

[irfytw]

Thanks, I did try that, however, unsuccessfully. I’m sure I’m doing something incorrectly but it was not immediately obvious.
I am certain my solution is over-engineered, but it works well.
Cheers,
Tyler

[szawadski]

Also using your method. Just replacing the regex by a Column Expressions node to add the quotes.
Never tried the DB Looping node but I’ll probably try the next time I need this.

[izaychik63]

Here’s an example how to use the node

24_DB_Looping — NodePit

[irfytw]

Are you saying to add the quotes prior to the concatenate? That would also work, yes but it’s just a node swap really. There is probably about 100 ways to make this work in Knime haha. I love all this collaboration. Thanks!