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