Example: Iterating Over the Elements of an Array Parameter

Suppose you want to create a data service that summarizes account information for a list of customers. The data service must take a parameter containing the array of account numbers and must produce a result set with a row for each account that includes:

• ACCOUNT_NUMBER
• LAST_TRANSACTION_DATE
• BALANCE
• NOTES

For simplicity, assume that there is already a database operation named “GetAccountInfo” that returns the LAST_TRANSACTION_DATE, BALANCE, and NOTES values for a single account, given the account’s number.

Using WorkSpace Data Federation, you create a view model with a parameter named “Accounts” of type INTEGER (array of integers). Then you use the GetAccountInfo database operation to create an Input Source element, connecting its output to a new Projection, the output of which goes into an new Iterator and, finally, to the Result element.

Configure the Iterator operator as follows:

• Name: Enter “AccountIterator” for the operator’s name. The name must match the name used in specifying the variables in the three fields below.

• Initialize: Enter the following code:

  // Set up the counter we'll use to index the array
  variables.AccountIterator.x = 0;
  // Get the first array element
  variables.AccountIterator.current =
      variables.global.Accounts[variables.AccountIterator.x];
  

• Increment: Enter the following code:

  //Increment the counter
  variables.AccountIterator.x++;
  // Get the next array element
  variables.AccountIterator.current =
      variables.global.Accounts[variables.AccountIterator.x];
  

• Condition: Enter the following code:

  // Check to see if we’re at the end of the array
  variables.AccountIterator.x <
      variables.global.Accounts.length
  

Configure the Projection operator next. Use the Project All button in the properties dialog to pass the results of the input source straight through. Then add a new column named ACCOUNT_NUMBER that references the current variable in the Iterator. (You can use the expressions menu to enter it.) The expression should be:

variables.AccountIterator.current

Finally, configure the Input Source parameter mapping. For the “AccountNumber” parameter, use this expression:

variables.AccountIterator.current

When you execute this view model, the Iterator operator iterates over the values in the Accounts array and executes the GetAccountInfo database operation once for each value, passing the next account number as a parameter. The Projection operator then adds the ACCOUNT_NUMBER column to the schema. The results of these executions are concatenated together and returned as the overall result of the model.