# 18.4 Iterative Models

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 (n) [UNTIL (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, s

FROM sales_history

MODEL

UNIQUE SINGLE REFERENCE

RETURN UPDATED ROWS

PARTITION 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, s

FROM sales_history

MODEL

UNIQUE SINGLE REFERENCE

RETURN UPDATED ROWS

PARTITION 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.375

6       2001          3  79628.875

7       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.

#### 18.4.1 Knowing how many iterations have occurred

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, i

FROM sales_history

MODEL

UNIQUE SINGLE REFERENCE

RETURN UPDATED ROWS

PARTITION 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.

##### 18.4.1.1 Referencing values from the previous iteration

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, i

FROM sales_history

MODEL

UNIQUE SINGLE REFERENCE

RETURN UPDATED ROWS

PARTITION 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, s

FROM sales_history

MODEL

UNIQUE SINGLE REFERENCE

RETURN UPDATED ROWS

PARTITION 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```  Mastering Oracle SQL, 2nd Edition  Preface  Chapter 1. Introduction to SQL  Chapter 2. The WHERE Clause  Chapter 3. Joins  Chapter 4. Group Operations  Chapter 5. Subqueries  Chapter 6. Handling Temporal Data  Chapter 7. Set Operations  Chapter 8. Hierarchical Queries  Chapter 9. DECODE and CASE  Chapter 10. Partitioning  Chapter 11. PL/SQL  Chapter 12. Objects and Collections  Chapter 13. Advanced Group Operations  Chapter 14. Advanced Analytic SQL  Chapter 15. SQL Best Practices  Chapter 16. XML  Chapter 17. Regular Expressions  Chapter 18. Model Queries  18.1 Basic Elements of a Model Query  18.2 Cell References  18.3 Rules  18.4 Iterative Models  18.5 Reference Models  Appendix A. Oracle's Old Join Syntax  Colophon