## MODEL Clause

### Expressions and Cell References

In this tutorial we continue exploring the RULES part of the MODEL clause. We will focus on cell references in the right hand side of rule equations. Subsequent tutorials will cover other aspects of rules.

#### Expressions

In the Basic Rules and Cell Assignments tutorial we saw rules like these which assign the constants 100, 200, and 300 to certain cells.

```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[ 2 ] = 200 ,
M_1[ 3 ] = 300
)
order by
key
;
```
```   KEY NUM_VAL        M_1
------ ------- ----------
1     100        100
2     200        200
3     300        300
4                  0
5     300          0
6     100          0
7     100          0
8                  0
9     200          0
10     800          0
```

We can also use more complex expressions on the right hand side of a rule, like these.

```variable BIND_VAR number

execute :BIND_VAR := 4000000

select
key ,
num_val ,
m_1
from
t
model
dimension by ( key )
measures     ( num_val, 0 as m_1 )
rules
( m_1[ 1 ] = NUM_VAL[ 1 ] * 10 ,
m_1[ 2 ] = TO_NUMBER( TO_CHAR( SYSDATE, 'YYYYMMDD' ) ),
m_1[ 3 ] = DBMS_UTILITY.GET_TIME ,
m_1[ 4 ] = :BIND_VAR
)
order by
key
;
```
```   KEY NUM_VAL        M_1
------ ------- ----------
1     100       1000
2     200   20070228
3     300    9081636
4            4000000
5     300          0
6     100          0
7     100          0
8                  0
9     200          0
10     800          0
```

#### Cell References

The term "NUM_VAL[1]" in the last example is called a "cell reference". Cell references can only refer to measure cells and they must only refer to a single measure cell when used on the right hand side of a rule.

The part between the square brackets in a cell reference is called a dimension reference and it can only contain literal values (e.g. "1"), expressions (e.g. "1+1"), or conditions (e.g. "KEY=3") that resolve to a single dimension value. Here are some examples.

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

If required, you can nest one cell reference within another as long as 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.

```select
key ,
num_val ,
m_1
from
t
model
dimension by ( key )
measures     ( num_val, 0 as m_1 )
rules        ( m_1[ NUM_VAL[1]/100 ] = 10 )
order by
key
;
```
```   KEY NUM_VAL        M_1
------ ------- ----------
1     100         10
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
```

#### UNIQUE SINGLE REFERNCE

As described in DIMENSION BY the UNIQUE SINGLE REFERENCE feature allows you to have duplicate domain values in your result set. You can only use this feature as long as you do not include any multi-cell references on the right hand side of your query rules. For example, this is o.k.

```select
group_2 ,
num_val ,
m_1
from
t
model UNIQUE SINGLE REFERENCE
dimension by ( group_2 )
measures     ( num_val, 0 as m_1 )
rules        ( m_1[any] = 10 )
order by
group_2
;
```
```GROUP_2    NUM_VAL        M_1
---------- ------- ----------
a1             100         10
a1             100         10
a1             200         10
a1                         10
a2             800         10
a2             200         10
a2             300         10
a2                         10
a3             300         10
a3             100         10
```

but this is not

```select
group_2 ,
num_val ,
m_1
from
t
model UNIQUE SINGLE REFERENCE
dimension by ( group_2 )
measures     ( num_val, 0 as m_1 )
rules        ( m_1[any] = NUM_VAL['a2'] * 10 )
;
t
*
ERROR at line 6:
ORA-32638: Non unique addressing in MODEL dimensions

```

and neither is this.

```select
group_2 ,
num_val ,
m_1
from
t
model UNIQUE SINGLE REFERENCE
dimension by ( group_2 )
measures     ( num_val, 0 as m_1 )
rules        ( m_1[any] = NUM_VAL[CV()] * 10 )
;
t
*
ERROR at line 6:
ORA-32638: Non unique addressing in MODEL dimensions

```

#### Gotchas

You cannot reference columns in rules. In the following rule the column NUM_VAL is referenced illegally. Use a cell reference like "NUM_VAL[3]" instead.

```select
key ,
num_val ,
m_1
from
t
model
dimension by ( key )
measures     ( num_val, 0 as m_1 )
rules        ( m_1[3] = NUM_VAL * 10 )
order by
key
;
rules        ( m_1[3] = NUM_VAL * 10 )
*
ERROR at line 10:
ORA-00984: column not allowed here

```

Including a cell reference to a dimension column will produce an error. Only measure columns are allowed in cell references.

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

```

Multi-cell references, like NUM_VAL[ANY], are not allowed on the right hand side of a rule.

```select
key ,
num_val ,
m_1
from
t
model
dimension by ( key )
measures     ( num_val, 0 as m_1 )
rules        ( m_1[any] = NUM_VAL[ANY] )
;
rules        ( m_1[any] = NUM_VAL[ANY] )
*
ERROR at line 10:
ORA-32622: illegal multi-cell reference

```

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)] / 100 ] = 1 )
order by
key
;
select
*
ERROR at line 1:
ORA-32622: illegal multi-cell reference

```

Even though a dimension expression resolves to a single value, it may still trigger an error.

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

```

You may not use subqueries in rules, except in the FOR construct (described in FOR Loops).

```select
key ,
dummy
from
t
where
key = 1
model
dimension by ( key )
measures     ( 'Y' as dummy )
rules        ( m_1[1] = ( SELECT DUMMY FROM DUAL ) )
;
rules        ( m_1[1] = ( SELECT DUMMY FROM DUAL ) )
*
ERROR at line 11:
ORA-00904: : invalid identifier

```

Instead, define the subquery in the MEASURES list.

```select
key ,
dummy
from
t
where
key = 1
model
dimension by ( key )
measures     ( ( SELECT DUMMY FROM DUAL ) as dummy )
rules        ( )
;
```
```   KEY D
------ -
1 X
```

#### 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-11774.html]SQL Snippets: MODEL Clause - Expressions and Cell References[/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-11774.html">SQL Snippets: MODEL Clause - Expressions and Cell References</a>

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

• Link Text : SQL Snippets: MODEL Clause - Expressions and Cell References
• URL (href): http://www.sqlsnippets.com/en/topic-11774.html