FOR_EACH

FOR_EACH - a coding pattern

The FOR_EACH statement performs iteration over an aggregate relationship. A control variable is used to hold an instance value from the aggregate for each iteration.

FOR_EACH variable OVER aggregate(relationship)name

BEGIN END;

The instance value from each iteration is held in a control variable. You can use the pseudo-attribute $getPos to obtain the current iterator count. Use the pseudo-attribute $getSize to obtain the total number of instances in an iterator. (All counting in Transformation Manager begins at 1.)

Example

You have a personnel system as your source database. The Qualification column in the QUAL table holds qualification details for each member of staff. One record for each qualification for each member of staff. Your target model is expecting that data in a denormalised form. i.e. one row or node will hold all the qualification details for each member of staff. Your source table is the EMPLOYEE table which is related to QUAL via the relToQUALS relationship.

For each person you need to:

define a text variable to hold the string of qualifications you are going build up.
loop through their qualifications one at a time, adding each qualification in turn to the text string.
Write the completed text string to the target outside the For_Each loop.

      LOCAL

          TextQuals : 
	string;

	     
END_LOCAL;



	      FOR_EACH person 
	OVER relToQUALS;

 
   
    BEGIN
 
             TextQuals := TextQuals & 
	' ' & person.Qualification;

      
    END;



      Target.Quals     := TextQuals;

Notes:

TextQuals is the local variable used to build up the qualification string.
person is the control variable.
relToQUALS is the name of the relationship from EMPLOYEE to QUAL.
& is used for concatenation.
' ' ensure there is a space between each qualification.
person.Qualification has to be entered manually. It is the syntax that links the control variable (person) to the instance value (the Qualification column in the QUAL table)
The Target.Quals := TextQuals; is the assign statement to the target.

Eg one person had 5 qualifications in the QUAL table as follows:

MA
MB BChir
MRCS
D.Obst
FPCert

using the above code, a single string with the value MA, MB BChir, MRCS, D.Obst, FPCert would be assigned to Target.Quals.

Click here for more examples of FOR_EACH.