## MODEL Clause

### Basic Rules and Cell Assignments

In this tutorial we start to explore the RULES part of the MODEL clause. A rule is simply a statement that assigns a value to a measure.

In this tutorial we will focus on basic rules and cell assignments. Subsequent tutorials will cover other aspects of rules.

#### Basic Rules

In the preceeding tutorial we learned how to specify the DIMENSION BY and MEASURES clauses in a SELECT statement like this.

```select
key ,
num_val ,
m_1
from
t
model
dimension by ( key )
measures     ( num_val, 0 as m_1 )
rules        ( )
order by
key
;
```
```   KEY NUM_VAL        M_1
------ ------- ----------
1     100          0
2     200          0
3     300          0
4                  0
5     300          0
6     100          0
7     100          0
8                  0
9     200          0
10     800          0
```

We can use the RULES clause to change any value appearing under a measure column such as M_1 like this.

```select
key ,
num_val ,
m_1
from
t
model
dimension by ( key )
measures     ( num_val, 0 as m_1 )
RULES
( M_1[ 1                   ] = 100 ,
M_1[ KEY = 2             ] = 200 ,
M_1[ KEY BETWEEN 4 AND 7 ] = 300
)
order by
key
;
```
```   KEY NUM_VAL        M_1
------ ------- ----------
1     100        100
2     200        200
3     300          0
4                300
5     300        300
6     100        300
7     100        300
8                  0
9     200          0
10     800          0
```

#### Cell Assignments

The term "M_1" is called a "cell reference" and it refers to the M_1 column value where the KEY column value is "1" in the SELECT command's output. Cell references can only refer to measure cells, not dimension cells. When a cell reference is used as the assignment target on the left side of a rule equation it is called a "cell assignment". Note that the term "cell assignment" refers only to the term on the left hand side of the rule equation, not the entire equation.

On the right hand side of a rule cell references do not have a special name. To avoid confusion, we will always refer to the cell(s) targeted by a rule only as a "cell assignment" and we will use "cell reference" in all other cases.

The part between the square brackets, "[]", is called a "dimension reference". When a dimension reference appears in a cell assignment it can be a condition, expression, or loop construct (described in subsequent tutorials) targeting one or more dimension values. Dimension references for all other cell references can only contain conditions or expressions.

To create a cell assignment targeting all cells in a measure column use the ANY keyword in the dimension reference.

```select
key ,
num_val ,
m_1
from
t
model
dimension by ( key )
measures     ( num_val, 0 as m_1 )
rules        ( m_1[ ANY ] = 100 )
order by
key
;
```
```   KEY NUM_VAL        M_1
------ ------- ----------
1     100        100
2     200        100
3     300        100
4                100
5     300        100
6     100        100
7     100        100
8                100
9     200        100
10     800        100
```

You can nest a cell reference within a cell assignment if the nested cell reference only identifies a single cell. Other restrictions on nested cell references are documented at Rules and Restrictions when Using SQL for Modeling.

```column key_equals_num_val_2 format a16

select
key ,
num_val ,
key_equals_num_val_2
from
t
model
dimension by ( key )
measures     ( num_val, 'N' as key_equals_num_val_2 )
rules        ( key_equals_num_val_2[ NUM_VAL/100 ] = 'Y' )
order by
key
;
```
```   KEY NUM_VAL KEY_EQUALS_NUM_V
------ ------- ----------------
1     100 N
2     200 Y
3     300 N
4         N
5     300 N
6     100 N
7     100 N
8         N
9     200 N
10     800 N
```

A cell whose dimension is null will be targeted by any of the following styles of dimension reference.

• [any]
• [group_1 is any]
• [null]
• [group_1 is null]

A reference like "[group_1 = null]" will not work.

```select
group_1 ,
using_any ,
using_is_any ,
using_null ,
using_is_null ,
using_equals_null
from
t
where
key in ( 1, 10 )
model
dimension by( group_1 )
measures
(
'N' as using_any ,
'N' as using_is_any ,
'N' as using_null ,
'N' as using_is_null ,
'N' as using_equals_null
)
rules
(
using_any        [ ANY             ] = 'Y' ,
using_is_any     [ GROUP_1 IS ANY  ] = 'Y' ,
using_null       [ NULL            ] = 'Y' ,
using_is_null    [ GROUP_1 IS NULL ] = 'Y' ,
using_equals_null[ GROUP_1 = NULL  ] = 'Y'
)
;
```
```GROUP_1    USING_ANY USING_IS_ANY USING_NULL USING_IS_NULL USING_EQUALS_NULL
---------- --------- ------------ ---------- ------------- -----------------
A          Y         Y            N          N             N
Y         Y            Y          Y             N
```

#### Gotchas

Attempting to modify a dimension column with a rule will generate an error. The left side of a rule can only reference measure cells.

```select
key ,
num_val
from
t
model
dimension by ( key )
measures     ( num_val )
rules        ( KEY = 0 )
order by
key
;
rules        ( KEY = 0 )
*
ERROR at line 9:
ORA-00904: : invalid identifier

```

Specifying too few dimension values in a cell reference results in an ORA-00947 error.

```select
key ,
m_1
from
t
model
dimension by ( group_1, group_2 )
measures     ( 0 as m_1 )
rules        ( m_1['A'] = 100 )
;
rules        ( m_1['A'] = 100 )
*
ERROR at line 9:
ORA-00947: not enough values

```

Specifying too many dimension values in a cell reference results in the same ORA-00947 error as the last example. In this case the error text "not enough values" is misleading.

```select
key ,
m_1
from
t
model
dimension by ( key )
measures     ( 0 as m_1 )
rules        ( m_1[1,2] = 100 )
;
rules        ( m_1[1,2] = 100 )
*
ERROR at line 9:
ORA-00947: not enough values

```

Including a dimension column on the right side of a dimension reference expression will produce an error.

```select
group_1 ,
group_2 ,
m_1
from
t
model
dimension by ( group_1, group_2 )
measures     ( 'N' as m_1 )
rules        ( m_1[ any, group_2 > GROUP_1 ] = 'Y' )
order by
group_1
;
rules        ( m_1[ any, group_2 > GROUP_1 ] = 'Y' )
*
ERROR at line 10:
ORA-32625: illegal dimension in cell reference predicate

```

A nested cell reference that targets more than one cell will produce an error.

```
select
key ,
num_val ,
m_1
from
t
model
dimension by ( key )
measures     ( num_val, 0 as m_1 )
rules        ( m_1[ NUM_VAL[KEY IN (1,2,3)] ] = 1 )
order by
key
;
select
*
ERROR at line 1:
ORA-32622: illegal multi-cell reference

```

#### 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-11667.html]SQL Snippets: MODEL Clause - Basic Rules and Cell Assignments[/url]