## MODEL Clause

### The CV() Function

In this tutorial we continue exploring the RULES part of the MODEL clause. We will focus on the CV() function here. Subsequent tutorials will cover other aspects of rules.

If we need to access the value of a dimension cell, we cannot do it using a cell reference because cell references can only refer to measure columns. For example, we cannot use a cell reference like `KEY[ CV() ]` to access the value of KEY cells.

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

```

Fortunately the CV() function exists to helps us access the current value of any dimension cell. In the following example `CV(KEY)` returns the current value of the KEY dimension column.

```select
key ,
m_1
from
t
model
dimension by ( key )
measures     ( 0 as m_1 )
rules        ( m_1[any] = CV(KEY) * 10 )
order by
key
;
```
```   KEY        M_1
------ ----------
1         10
2         20
3         30
4         40
5         50
6         60
7         70
8         80
9         90
10        100
```

You can use CV() without an argument in dimension references to return the current dimension value associated with its relative position within a cell reference. For example, in the following snippet the first occurrence of `CV()` refers to the current value of the GROUP_1 dimension and the second occurrence refers to the current value of the GROUP_2 dimension.

```select
group_1 ,
group_2 ,
num_val ,
m_1
from
t
model
dimension by ( group_1, group_2 )
measures
(
num_val,
0 as m_1
)
rules
(
m_1[any,any] = NUM_VAL[ CV(), CV() ] * 10
)
order by
group_1 ,
group_2
;
```
```GROUP_1    GROUP_2    NUM_VAL        M_1
---------- ---------- ------- ----------
A          a1             100       1000
A          a2             200       2000
A          a3             300       3000
B          a1
B          a2             300       3000
B          a3             100       1000
C          a1             100       1000
C          a2
a1             200       2000
a2             800       8000
```

CV() also allows us to use relative indexing, as demonstrated in the dimension reference `[ CV() - 1 ]` for the RUNNING_TOTAL measure in this snippet.

```break on group_1 skip 1 duplicates

select
key ,
num_val ,
running_total
from
t
model
dimension by ( key )
measures
(
nvl(num_val,0) as num_val ,
0 running_total
)
rules
(
running_total[ 1       ] = num_val[cv()] ,
running_total[ key > 1 ] = num_val[cv()] + running_total[ CV() - 1 ]
)
order by
key
;
```
```   KEY NUM_VAL RUNNING_TOTAL
------ ------- -------------
1     100           100
2     200           300
3     300           600
4       0           600
5     300           900
6     100          1000
7     100          1100
8       0          1100
9     200          1300
10     800          2100
```

#### Gotchas

CV() cannot be used on measure columns.

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

```

CV() cannot be used outside of a dimension reference without an argument.

```select
key ,
num_val ,
m_1
from
t
model
dimension by ( key )
measures     ( num_val, 0 as m_1 )
rules        ( m_1[any] = CV() * 10 )
order by
key
;
rules        ( m_1[any] = CV() * 10 )
*
ERROR at line 10:
ORA-32611: incorrect use of MODEL CV operator

```

#### 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-11776.html]SQL Snippets: MODEL Clause - The CV() Function[/url]