Break an SQL IN Clause Into Batches

Scenario

Data enrichment via a DB lookup may use a SQL "IN" clause, but sometimes the number of values in the IN clause exceeds the number of values allowed in a single IN clause by the database. In that case, it becomes necessary to break up the values in the IN clause into separate blocks so that the database can process them.

Create a DB collector to retrieve data from a database. This collector should have a "where ... IN" clause in it, match to a list of values. Collect all the data in the table using multiple calls to the collector, each with a different set of values in the IN clause.

Solution:

• Create a database collector to load some data into a stream in PhixFlow
• Create an Input Multiplier on that stream in PhixFlow
• Configue the input multiplier to compile the Ids from this stream into 2 separate Input Multiplier elements (lists) for use in an IN clause in the DB collector