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

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.
    Constant Column
  • 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.
    row_to_variable
  • 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.
    Flow

Cheers,
Tyler

2 Likes

I would recommend to use

node instead of injecting SQL.

1 Like

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

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

Here’s an example how to use the node

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!

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.