How to manage an 'IN' clause
In Metastorm BPM 9 we have a problem that you cannot safely define a query for a Business Object with an 'IN' clause. This makes some dynamic query forms, such as search forms, impossible to write if multiple options can be selected form a list.
One approach we have come across that gets around this issue is to create some SQL that inserts ID values (in this and most cases, a Folder ID) into a table. The resulting BO need only join to that table, and all linked records can be listed.
There are many approaches to cleaning up the table to avoid old records remaining. One is to delete on commit (although you cannot guarantee a commit). Another is to add a time stamp and delete all records over 24 hours old. We use that in the second example.
In some cases you may need to de-fragment the indexes and/or rebuild the table over time.