• 1 reply
  • Latest Post - ‏2012-09-27T18:03:10Z by kevintap
4 Posts

Pinned topic "For Each" operation using Transform Modify

‏2012-09-27T11:24:33Z |
I read that WEF uses Transform Modify to deliver the same function as the IBM MashupCenter "For Each" operator.
Unfortunately, I'm having some difficulty making this work.
The scenario is as follows:
I have an SQL call which returns details about systems - Type, Model, Feature, Serial .
I have imported a spreadsheet which has a few specific serial numbers in it.
The required result is to provide details from the SQL call for only the serial numbers in the spreadsheet.

In MashupCenter, I would have joined the two inputs with a "For Each" operator. (For Each element in Input1 the spreadsheet run Input2 the SQL call. The SQL needs a variable for "where serial=':serno' " This variable is substituted from the spreadsheet input in the "For Each" operation. The result would be a listing containing Type Serial Model and all the Features associated with that particular serial number.

The question is - how do I do that in WEF? Has anyone done this yet - or something similar?

Updated on 2012-09-27T18:03:10Z at 2012-09-27T18:03:10Z by kevintap
  • kevintap
    756 Posts

    Re: "For Each" operation using Transform Modify

    There are a couple of ways that you can go about this.

    The Transform Modify builder isn't ideally suited to the "for each" case that you describe. However, the Transform Modify could be used to accomplish what you need. The builder basically provides a way to iterate through a repeated set of elements and perform some action on those elements. If you choose Custom Method IXml as the action, this gives you the ability to execute Method (or Action List) in your model that returns IXml which will replace the original element. In this way, you could have a custom method execute the SQL query to populate your data structure. The drawback to this approach is that you're making multiple round trips to the database.

    Another approach that you might consider is invoking a single SQL query which returns the data you need (for all IDs). This can then be merged with the spreadsheet data using the Transform Merge builder. This approach may work well, but may present some performance concerns if there is a large volume of data being retrieved by executing a single SQL query (without the where clause to filter the data).

    A third possible approach might be a hybrid approach to use something like a Transform Group builder and/or a Transform Aggregate builder to build a unique set of IDs for which you need to run the SQL query. Then you could have your SQL query only select the set of records that you actually need (using a where clause with an IN operator). And finally, use a Transform Merge to merge the results of this SQL with the data from your spreadsheet.

    The postings on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.