Iteration is a powerful tool in mathematical modeling. Some applications involving approximate calculations involve iterative computation. Usually developers resort to procedural languages to implement iteration. The MODEL clause provides a way to write iterate code using SQL.

At times, you may need to evaluate the rules of a model repeatedly, until some sort of condition is met. MODEL's ITERATE subclause provides the required functionality to iterate rules. The syntax of the ITERATE subclause, which is a part of the RULES clause, is:

RULES [UPSERT | UPDATE] [SEQUENTIAL ORDER | AUTOMATIC ORDER] ITERATE () [UNTIL (n)]condition

The syntax elements are:

*n*-
A positive number specifying the number of iterations.

*condition*-
An early-termination condition.

The
early-termination
condition is optional. If specified, the condition is evaluated at
the end of every iteration. If you specify an early-termination
condition, iteration ends when that condition is satisfied,
regardless of whatever value you specify for `n`.

The following example illustrates iteration of rules:

SELECT r, y, m, sFROM sales_historyMODELUNIQUE SINGLE REFERENCERETURN UPDATED ROWSPARTITION BY (region_id r)DIMENSION BY (year y, month m)MEASURES (sales s)RULES ITERATE (4)(s[2001,3] = s[2001,3] / 2)ORDER BY y, r, m;R Y M S ----- ---------- ---------- ---------- 5 2001 3 35405.1875 6 2001 3 39814.4375 7 2001 3 40999.5625

In this query, the cell `s[2001,3]` is computed by
dividing `s[2001,3]` by 2 four times. You can stop
the iteration before all four iterations are executed, by using the
UNTIL option,
as illustrated in the following example:

SELECT r, y, m, sFROM sales_historyMODELUNIQUE SINGLE REFERENCERETURN UPDATED ROWSPARTITION BY (region_id r)DIMENSION BY (year y, month m)MEASURES (sales s)RULES ITERATE (4) UNTIL (s[2001,3] < 100000)(s[2001,3] = s[2001,3] / 2)ORDER BY y, r, m;R Y M S----- ---------- ---------- ----------5 2001 3 70810.3756 2001 3 79628.8757 2001 3 81999.125

In this latest example, after every iteration, the condition in the
UNTIL clause is evaluated. If the condition is false, the next
iteration starts; if the condition is true, the iterative evaluation
stops. In this case, once the value of the cell
`s[2001,3]` drops below 100,000, the iteration
stops. Compare the two preceding examples, and you will find that the
second query didn't perform all four iterations.

Sometimes it's useful to know how many iterations have occurred. Oracle provides a useful function (also referred to as a system variable) called ITERATION_NUMBER to keep a count of the number of iterations through a set of rules. The function returns the current iteration number. It starts with 0 for the first iteration, and increments by 1 for every subsequent iteration. So, after a query has completed four iterations, ITERATION_NUMBER will return 3 (iterations 0, 1, 2, and 3). The following example illustrates how you can get the iteration number from a query:

SELECT r, y, m, s, iFROM sales_historyMODELUNIQUE SINGLE REFERENCERETURN UPDATED ROWSPARTITION BY (region_id r)DIMENSION BY (year y, month m)MEASURES (sales s, 0 i)RULES ITERATE (4) UNTIL (s[2001,3] < 100000)(s[2001,3] = s[2001,3] / 2,i[2001,3] = ITERATION_NUMBER)ORDER BY y, r, m;R Y M S I ----- ---------- ---------- ---------- ---------- 5 2001 3 70810.375 2 6 2001 3 79628.875 2 7 2001 3 81999.125 2

Returning the iteration number takes a bit of innovative coding. In
the preceding example, a new measure called `i` has
been introduced to capture the iteration number. This measure is
updated by the output of the function ITERATION_NUMBER at every
iteration. Since `i` doesn't
correspond to any column of the table, we initialized the measure
`i` to the constant `0`; we did
that in the MEASURES clause. We could have used any constant value to
initialize `i`, and the output would still be the
same. This is because the initial value of the measure
`i` is updated to 0 during the first iteration, and
then incremented by 1 each iteration thereafter.

Another useful feature when using iterations is the PREVIOUS function. The PREVIOUS function takes a single cell reference as input, and returns the value of the cell as it existed after the previous iteration, just before the current iteration began. The following example illustrates how you can use the PREVIOUS function to get the value of a cell as it existed after the previous iteration:

SELECT r, y, m, s, iFROM sales_historyMODELUNIQUE SINGLE REFERENCERETURN UPDATED ROWSPARTITION BY (region_id r)DIMENSION BY (year y, month m)MEASURES (sales s, 'a' i)RULES ITERATE (4)UNTIL ( (PREVIOUS(s[2001,3]) - s[2001,3]) < 100000 )(s[2001,3] = s[2001,3] / 2,i[2001,3] = ITERATION_NUMBER)ORDER BY y, r, m;R Y M S I ----- ---------- ---------- ---------- - 5 2001 3 70810.375 2 6 2001 3 79628.875 2 7 2001 3 81999.125 2

In this example, the previous value of the cell is compared to the current value after every iteration, and if that difference is less than 100,000, then the iteration stops. The PREVIOUS function provides a very useful means of constructing a termination condition. For problems requiring approximation solutions, you can use this approach to iterate till a point when the difference between the previous value and the current value is less than a threshold. You can then assume you have arrived at a reasonably approximate solution. For example, you could calculate pi to a resolution of 1/1000.

Iteration works only with the sequential ordering of rules. If you attempt to use ITERATE along with AUTOMATIC ORDER, you will get an error, as shown in the following example:

SELECT r, y, m, sFROM sales_historyMODELUNIQUE SINGLE REFERENCERETURN UPDATED ROWSPARTITION BY (region_id r)DIMENSION BY (year y, month m)MEASURES (sales s)RULES AUTOMATIC ORDER ITERATE (4)(s[2001,3] = 20000,s[2002,3] = s[2001,3] / 2)ORDER BY y, r, m;RULES AUTOMATIC ORDER ITERATE (4) * ERROR at line 8: ORA-32607: invalid ITERATE value in SPREADSHEET clause