Aggregation of IDs for IN clause in a Delete DB Snap

[skidambi]

When a Database Delete (Example: SQL Server Delete) snap is used, the snap goes through each of the entries in the incoming document and makes one roundtrip to the database to delete the pertinent record. Whilst it could be argued that this is not the efficient method, the snap does provide a great relief in providing the user with the ability to be able to use the IN keyword. Hence, all of the pertinent record info necessary for the delete operation can be aggregated using a simple procedure (see attached pipeline). This way all of the deletes can be performed in one step using the delete snap.

Steps Involved:

1. Concatenate all of the pertinent IDs from the incoming document using an aggregate snap (Use the UNIQUE_CONCAT function and Unsorted option for Sorted Streams, Example: $KeyID to $KeyIDs in the sample pipeline provided).
2. Use a mapper snap and provide the following expression to the target path (expression is based on the example provided in the sample pipeline):
   “(”+$[‘$KeyIDs’].replace(/|/g,“,”)+“)”
3. The above expression converts the IDs to a comma separated list of values bound by parenthesis (as a string) as output.
4. Feed the output of the mapper snap to the delete snap.

Aggregate-IDs_2017_02_26.slp (5.5 KB)