SQL Snippets ™: Tutorials for Oracle Developers

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