## MODEL Clause

### SEQUENTIAL and AUTOMATIC ORDER

In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on the order of evaluation when multiple rules are defined.

#### SEQUENTIAL ORDER

By default, Oracle evaluates rules in the order they appear in the RULES clause. This is know as SEQUENTIAL ORDER. For example, consider the following query.

```select
key, m1, m2, m3
from
t
where
key <= 3
model
ignore nav
dimension by ( key )
measures     ( 0 as m1, 0 as m2, 0 as m3 )
rules
update
SEQUENTIAL ORDER  -- this line optional, SEQUENTIAL ORDER is the default
(
m1[any] = m2[ cv() + 1 ] ,  -- rule 1
m2[any] = cv(key)        ,  -- rule 2
m3[any] = m2[ cv() + 1 ]    -- rule 3
)
order by
key
;
```
```   KEY         M1         M2         M3
------ ---------- ---------- ----------
1          0          1          2
2          0          2          3
3          0          3          0
```

The results show that processing occured in this fashion

1. Rule 1 is applied to cell m1
2. Rule 1 is applied to cell m1
3. Rule 1 is applied to cell m1
4. Rule 2 is applied to cell m2
5. Rule 2 is applied to cell m2
6. Rule 2 is applied to cell m2
7. Rule 3 is applied to cell m3
8. Rule 3 is applied to cell m3
9. Rule 3 is applied to cell m3

not this fashion.

1. Rule 1 is applied to cell m1
2. Rule 2 is applied to cell m2
3. Rule 3 is applied to cell m3
4. Rule 1 is applied to cell m1
5. Rule 2 is applied to cell m2
6. Rule 3 is applied to cell m3
7. Rule 1 is applied to cell m1
8. Rule 2 is applied to cell m2
9. Rule 3 is applied to cell m3

If you need the second behaviour, you can use SEQUENTIAL ORDER with the ITERATE feature described in the ITERATE tutorial.

```select
key, m1, m2, m3
from
t
where
key <= 3
model
ignore nav
dimension by ( key )
measures     ( 0 as m1, 0 as m2, 0 as m3 )
rules
update
SEQUENTIAL ORDER
ITERATE (3)
(
m1[ITERATION_NUMBER+1] = m2[ cv() + 1 ] ,  -- rule 1
m2[ITERATION_NUMBER+1] = cv(key)        ,  -- rule 2
m3[ITERATION_NUMBER+1] = m2[ cv() + 1 ]    -- rule 3
)
order by
key
;
```
```   KEY         M1         M2         M3
------ ---------- ---------- ----------
1          0          1          0
2          0          2          0
3          0          3          0
```

#### AUTOMATIC ORDER

In addition to SEQUENTIAL ORDER there is another rule ordering behaviour available. It is called AUTOMATIC ORDER. With AUTOMATIC ORDER Oracle determines rule evaluation order based on the dependencies between rules. Consider the following example.

```select
key, m1, m2, m3
from
t
where
key <= 3
model
ignore nav
dimension by ( key )
measures     ( 0 as m1, 0 as m2, 0 as m3 )
rules
update
AUTOMATIC ORDER
(
m1[any] = m2[ cv() + 1 ] ,  -- rule 1
m2[any] = cv(key)        ,  -- rule 2
m3[any] = m2[ cv() + 1 ]    -- rule 3
)
order by
key
;
```
```   KEY         M1         M2         M3
------ ---------- ---------- ----------
1          2          1          2
2          3          2          3
3          0          3          0
```

Unlike SEQUENTIAL ORDER, these results show that AUTOMATIC ORDER caused Oracle to evaluate rule 2 before rules 1 and 3 because rules 1 and 3 depend on the measure modified by rule 2.

#### Multiple Cell Assignments

With SEQUENTIAL ORDER the same cell can be assigned a value many different times.

```select
key, m1
from
dual
model
dimension by ( 0 as key )
measures     ( 0 as m1 )
rules SEQUENTIAL ORDER
(
M1 = 10 ,
M1 = 20 ,
M1 = 30
)
;
```
```   KEY         M1
------ ----------
0         30
```

With AUTOMATIC ORDER you can assign a value to a cell only once. Attempting to do otherwise will produce an error.

```select
key, m1
from
dual
model
dimension by ( 0 as key )
measures     ( 0 as m1 )
rules AUTOMATIC ORDER
(
M1 = 10 ,
M1 = 20 ,
M1 = 30
)
;
M1 = 20 ,
*
ERROR at line 11:
ORA-32630: multiple assignment in automatic order MODEL

```

#### Gotchas

You cannot use ITERATE with AUTOMATIC ORDER.

```select
key, m1, m2, m3
from
t
where
key <= 3
model
dimension by ( key )
measures     ( 0 as m1, 0 as m2, 0 as m3 )
rules
AUTOMATIC ORDER
ITERATE (3)
(
m1[iteration_number+1] = sum( m2 )[any] ,  -- rule 1
m2[iteration_number+1] = 1              ,  -- rule 2
m3[iteration_number+1] = sum( m2 )[any]    -- rule 3
)
order by
key
;
ITERATE (3)
*
ERROR at line 12:
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-11688.html]SQL Snippets: MODEL Clause - SEQUENTIAL and AUTOMATIC ORDER[/url]