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[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[2]/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[1] = 0 )
order by
key
;
rules ( KEY[1] = 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