MODEL Clause

ITERATE

In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on rule iteration.

To repetetively execute rules use the ITERATE feature. For example, to repeat a rule exactly five times you can use ITERATE like this.

select
  key ,
  m_1
from
  dual
model
  dimension by ( 0 as key )
  measures     ( cast( 'm_1: ' as varchar2(20) ) as m_1 )
  rules
    ITERATE (5)
    ( m_1[0] = m_1[0] || 'x' )
;
 
   KEY M_1
------ --------------------
     0 m_1: xxxxx
 

The maximum number of iterations allowed (as tested in Oracle 10g XE) is 4,294,967,295 (i.e. 4Gig - 1).

If you specify more than one rule, let's use three as an example, processing proceeds as follows

  • Rule 1 is executed in the first iteration
  • Rule 2 is executed in the first iteration
  • Rule 3 is executed in the first iteration
  • Rule 1 is executed in the second iteration
  • Rule 2 is executed in the second iteration
  • Rule 3 is executed in the second iteration
  • ...
  • Rule 1 is executed in the X'th iteration
  • Rule 2 is executed in the X'th iteration
  • Rule 3 is executed in the X'th iteration

not

  • Rule 1 is executed X times
  • Rule 2 is executed X times
  • Rule 3 is executed X times

The following example demonstrates rule iteration order.

select
  key ,
  m_1
from
  dual
model
  dimension by ( 0 as key )
  measures     ( cast( null as varchar2(15) ) as m_1 )
  rules sequential order
    ITERATE (5)
    (
      m_1[0] = m_1[0] || 'x' ,  -- Rule 1
      m_1[0] = m_1[0] || 'y'    -- Rule 2
    )
;
 
   KEY M_1
------ ---------------
     0 xyxyxyxyxy
 

UNTIL()

You can specify an early termination condition using an UNTIL clause.

select
  key ,
  m_1
from
  dual
model
  dimension by ( 0 as key )
  measures     ( 0 as m_1 )
  rules
    iterate (100) UNTIL M_1[0] = 50
    ( m_1[0] = m_1[0] + 10 )
;
 
   KEY        M_1
------ ----------
     0         50
 

The UNTIL clause is checked at the end of each iteration. This means iterated rules will always be executed at least once.

select
  key ,
  m_1
from
  dual
model
  dimension by ( 0 as key )
  measures     ( 0 as m_1 )
  rules
    iterate (100) UNTIL ( 1 = 1 )
    ( m_1[0] = m_1[0] + 10 )
;
 
   KEY        M_1
------ ----------
     0         10
 

PREVIOUS()

If you need to, you can reference the previous value of a cell in the UNTIL clause using the PREVIOUS() function.

select
  key ,
  m_1
from
  dual
model
  dimension by ( 0 as key )
  measures     ( 0 as m_1 )
  rules
    iterate (100) until PREVIOUS( m_1[0] ) = 50
    ( m_1[0] = m_1[0] + 10 )
;
 
   KEY        M_1
------ ----------
     0         60
 

ITERATION_NUMBER

The ITERATION_NUMBER function lets you access the current iteration number in repetetive rule calculations.

select
  key ,
  m_1
from
  dual
model
  dimension by ( 0 as key )
  measures     ( cast( null as varchar2(60) ) as m_1 )
  rules
    iterate (3)
    (
      m_1[0] =
        m_1[0] ||
        'iteration_number=' ||
        to_char( ITERATION_NUMBER ) ||
        ' '
    )
;
 
   KEY M_1
------ ------------------------------------------------------------
     0 iteration_number=0 iteration_number=1 iteration_number=2
 

Note that ITERATION_NUMBER starts at "0".

Unlike PREVIOUS(), which can only be used in the UNTIL clause, ITERATION_NUMBER can appear anywhere in a main model rule. In the following example ITERATION_NUMBER is used in the UNTIL clause, as a dimension reference, and in an expression on the right side of a rule.

select
  key ,
  m_1
from
  dual
model
  dimension by ( 0 as key )
  measures     ( 0 as m_1 )
  rules
    upsert
    iterate (10) until ( ITERATION_NUMBER = 5 )
    (
      m_1[ITERATION_NUMBER] = ITERATION_NUMBER * 10
    )
order by
  key
;
 
   KEY        M_1
------ ----------
     0          0
     1         10
     2         20
     3         30
     4         40
     5         50
 

PARTITION BY

When the PARTITION BY clause is used ITERATE(n) causes rules to be iterated n times for each distinct partitioned value. For example, the following query repeats its rule five times for each of the three distinct values in GROUP_1, leading to a total of 15 rule iterations.

select group_1, iteration_count
from   ( select distinct group_1 from t where group_1 is not null ) t_a
model
  partition by ( group_1 )
  dimension by ( 0 as key )
  measures     ( cast( null as varchar2(15) ) as iteration_count )
  rules iterate( 5 )
    ( iteration_count[0] = iteration_count[0] || to_char(iteration_number) || ' ' )
;
 
GROUP_1    ITERATION_COUNT
---------- ---------------
A          0 1 2 3 4
B          0 1 2 3 4
C          0 1 2 3 4
 

Gotchas

You can only use PREVIOUS in the UNTIL clause. If you use it elsewhere you will get an error.

select
  key ,
  m_1
from
  dual
model
  dimension by ( 0 as key )
  measures     ( 0 as m_1 )
  rules
    iterate (5)
    ( m_1[0] = PREVIOUS(m_1[0]) + 10 )
;
    ( m_1[0] = PREVIOUS(m_1[0]) + 10 )
               *
ERROR at line 11:
ORA-32618: incorrect use of MODEL PREVIOUS function


 

Keep in mind that referencing the current value of a cell on the right hand side of an iterated rule automatically gives you the previous value (assuming no other rules change the cell). Attempting to use PREVIOUS in the example above was a redundant effort.

ITERATION_NUMBER cannot be used in DIMENSION BY or MEASURES clauses.

select
  key ,
  m_1
from
  dual
model
  dimension by ( ITERATION_NUMBER as key )  -- not allowed
  measures     ( 0 as m_1 )
  rules
    upsert
    iterate (10)
    (
      m_1[iteration_number] = iteration_number * 10
    )
order by
  key
;
  dimension by ( ITERATION_NUMBER as key )  -- not allowed
                 *
ERROR at line 7:
ORA-00904: "ITERATION_NUMBER": invalid identifier


 
select
  key ,
  m_1
from
  dual
model
  dimension by ( 0 as key )
  measures     ( ITERATION_NUMBER as m_1 )  -- not allowed
  rules
    upsert
    iterate (10)
    (
      m_1[iteration_number] = iteration_number * 10
    )
order by
  key
;
  measures     ( ITERATION_NUMBER as m_1 )  -- not allowed
                 *
ERROR at line 8:
ORA-00904: "ITERATION_NUMBER": invalid identifier


 

If you specify more than 4,294,967,295 iterations you will get an error.

select
  key ,
  m_1
from
  dual
model
  dimension by ( 0 as key )
  measures     ( 0 as m_1 )
  rules
    iterate ( 4294967296 ) UNTIL ( 1 = 1 )
    ( m_1[0] = m_1[0] + 10 )
;
    iterate ( 4294967296 ) UNTIL ( 1 = 1 )
              *
ERROR at line 10:
ORA-32607: invalid ITERATE value in MODEL clause


 
select
  key ,
  m_1
from
  dual
model
  dimension by ( 0 as key )
  measures     ( 0 as m_1 )
  rules
    iterate ( binary_float_infinity ) UNTIL ( 1 = 1 )
    ( m_1[0] = m_1[0] + 10 )
;
    iterate ( binary_float_infinity ) UNTIL ( 1 = 1 )
              *
ERROR at line 10:
ORA-32607: invalid ITERATE value in MODEL clause


 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11686.html]SQL Snippets: MODEL Clause - ITERATE[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11686.html">SQL Snippets: MODEL Clause - ITERATE</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: MODEL Clause - ITERATE
  • URL (href): http://www.sqlsnippets.com/en/topic-11686.html

Revision Notes

Date Category Note
2007-05-17 Revision Added section on PARTITION BY.