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