Item Iteration Order

Section - TM Language Reference

Note - this should not be confused with Execution Order.

In some instances it is important to control the order in which items are iterated.  For example:

• Where a transform specifies a series of actions to be carried out on a aggregate. 
• Where a transform is being called from a number of sources. 

By default, Transformation Manager will iterate through items as follows:

• In an XML source, items will normally be discovered in document order.
• In a database, items will appear in any order
• In Java classes items will appear in array order.

However, the order can be controlled in all of these situations.

If a Transform is dependent then the order should be specified in the source relationship that calls the Transform. Use the keyboard short cut Ctrl Y to discover the callers. (You can also use the Toggle Depend View on the Project | View Project dialog to display the transforms that call the current transform).  Add an order by clause to the underlying properties of the relationship, using the Advanced text box on the Relationship | Properties dialog.  (If you want several distinct orders on the same relationship use  Clone Relationship first and then name that relationship accordingly).

In XML sources, adding

    order by X [asc/desc]

(as in SQL) or X into the relationship’s Advanced field has identical effect.

The SML dot syntax is available in XML sources:

    order by A.B.value

This will order by the values obtained through relationships A, B and then the attribute value.

If a Transform is independent then the order can be specified in the Transform itself.  Add the statement in to the advanced text box on the Transform Properties dialog. An example could be:

    order by name;

(Note that in RDBMS''s the order by clause will be executed directly by the database.)

If an independent Transform is also called dependently then the order specified in the calling relationship will take precedence over that specified in the Transform.

SML also offers sorting on the target side. Again, the order must be specified in the Advanced field of the Transform Properties  dialog.  An example could be:

    order by X;

Logically it is best to think of this ordering as taking place after the Transform has executed. While in general source ordering is more logical there are circumstances in which target ordering is extremely beneficial.  For example, to create an XML document holding customer items ordered by an increasing value when the source document/RDBMS contains a large number of individual account items, a simple statement

    order by value;

added to the target relationship of the Transform will ensure that the expected results are produced. (Note that target ordering serves no purpose on a relational database.)