Sorter Transformation Example

You have a database table PRODUCT_ORDERS that contains information about all the orders which were placed by the customer.

Use the Sorter transformation on PRODUCT_ORDERS and specify the ORDER_ID as the sort key with direction as descending.

After sorting the data, the Data Integration Service passes the following rows out of the Sorter transformation:

You need to find out the total amount and item quantity for each order. You can use the result of the Sorter transformation as an input to an Aggregator transformation. Use sorted input in Aggregator transformation to improve performance.

When you do not use sorted input, the Data Integration Service performs aggregate calculations as it reads. The Data Integration Service stores data for each group until it reads the entire source to ensure that all aggregate calculations are accurate. If you use sorted input and do not presort data correctly, you receive unexpected results.

The Aggregator transformation has the ORDER_ID group by port, with the sorted input option selected. When you pass the data from the Sorter transformation, the Aggregator transformation groups ORDER_ID to calculate the total amount for each order.