ODI 11g: Implementing Loops

For Loop

In programming, we implement for loop as follows:

for (i = 0; i < 10; i++){//statements}

This is a simple loop that iterates ten times. If we parse the part in the parentheses, we can see that in the first part, we assign a value to a variable and in the second part, we define the condition. The last part is the change of the variable value per iteration.

In ODI 11g we can implement this as follows.

1. Create a variable. I created a variable called V_FOR_LOOP, which is numeric and does not have a refreshing code.

2. Create a package. I created a package called P_FOR_LOOP. I will have a screenshot of package’s final status when we complete all the steps.

3. Set variable. Set a value to our variable V_FOR_LOOP as an initialization value. I will set it as 0. 

4. Evaluate variable. Evaluate V_FOR_LOOP against the iteration condition. I will use “less than 5” as the iteration value. You can choose between the options as you wish or as per your requirements.

5. Place your statements. Now, it is time to place your statements, which will iterate. I will only put one interface.

6. Increment your variable. You can increment your variable one step using the SET VARIABLE object’s Increment option. I will increment by 1.

7. Connect the "Increment your variable" step to the "Evaluate variable" step. Until this step, every object was connected to its following object with an OK line. Now, connect Increment to Evaluate Value with an OK line. It will go back to the evaluation and iterate until the evaluation is false.

Here is how our package looks in final form:

For loop package

And the operator screen when we run the package:

For loop operator view

As seen above, the steps numbered 1, 2, and 3 repeat five times, then the Step 4 runs one more time, decides that V_FOR_LOOP < 5 is not true enough, and the package finishes its run.

While Loop

In programming, we can implement while loop as follows:

while (flag == true){//statements}

So, this will iterate unknown times until its condition becomes incorrect. 

Confession time: I have to admit that I have never felt the need to use the while loop in ETL/ODI, but you may need to.

Before implementing this step-by-step, I created a table including two columns c1 and flag, where I will use flag as my condition. My data is as follows:

Now, let’s implement the while loop:

1. Create a variable to hold flag value. I create a variable called V_WHILE_LOOP, which is alphanumeric and refreshes with: select flag from variable.test where c1 = #V_FOR_LOOP. I will use my V_FOR_LOOP to select flag values in this example. Your case will contain different logic than this example.

2. Create a package. I create a package named P_WHILE_LOOP.

3. Set Variable (in my case). Since I am refreshing my flag depending on V_FOR_LOOP, I set this as the first step.

4. Refresh flag. Refresh your flag variable.

5. Check Flag. Evaluate your flag variable.

6. Statements. Place your statements. I will put my sample interface and increment V_FOR_LOOP, as I will need this to reach an invalid flag.

7. Set your connections. Until the end of your statementsm every step will be connected by an OK. When you reach the end, connect it to Step 4 (so you will refresh, check, and start your statements again and again until the flag is false).

Here is a view of the package:

While loop package

And the view from operator:

While loop operator

You can see it hits the end when we refresh flag for the fifth time since it will return F as the flag value, which is not suitable to our condition.