In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on UPSERT behaviour with cell assignments for missing cells.

By default, if a cell assignment targets a cell that does not exist then UPSERT logic is used to process the results. With UPSERT logic Oracle may or may not add a new cell (plus the associated cells in the new cell's row) to the result set when it encounters a nonexistent dimension value, depending on the style of dimension reference used.

If a dimension reference uses a constant expression (aka "positional references") then existing cells are updated and missing cells are created. In the example below the cell assignment with the KEY value "5" points to an existing cell, so it updates the cell to "5". The ones with KEY values "0" and "-1" point to nonexistent dimension values so new cells are added to the results.

select key, key_2, m_1, m_2, m_3 from t model dimension by ( key ) measures ( key_2 , cast(null as number) m_1 , cast(null as number) m_2 , cast(null as number) m_3 ) rules UPSERT ( m_1[ 0 ] = 1, -- positional ref., nonexistent value m_2[ 5 ] = 2, -- positional ref., existing value m_3[ FOR KEY IN (-1,5) ] = 3 -- positional ref., mixed ) order by key ;

KEY KEY_2 M_1 M_2 M_3 ------ ----- ---------- ---------- ---------- -1 3 0 1 1 T-1 2 T-2 3 T-3 4 T-4 5 T-5 2 3 6 T-6 7 T-7 8 T-8 9 T-9 10 T-10

Note that FOR loops like
`FOR KEY IN (-1,5)`

are considered positional references.

If the dimension reference uses a boolean expression (aka a "symbolic reference")
that evaluates to FALSE, i.e. the cell assignment points to a missing cell,
then the rule is ignored.
In this example the rule for M_1 is applied where
`KEY = 5`

is TRUE.
The rule for M_2 is never applied because
`KEY = 0`

is always FALSE.

select key, key_2, m_1, m_2, m_3 from t model dimension by ( key ) measures ( key_2 , cast(null as number) m_1 , cast(null as number) m_2 , cast(null as number) m_3 ) rules UPSERT ( m_1[ KEY = 0 ] = 1 , -- symbolic ref, nonexistent dimension val m_2[ KEY = 5 ] = 2 , -- symbolic ref, existing dimension val m_3[ KEY IN (-1,5) ] = 3 -- symbolic ref, mixed ) order by key ;

KEY KEY_2 M_1 M_2 M_3 ------ ----- ---------- ---------- ---------- 1 T-1 2 T-2 3 T-3 4 T-4 5 T-5 2 3 6 T-6 7 T-7 8 T-8 9 T-9 10 T-10

Note that, unlike
`FOR KEY IN (-1,5)`

,
the dimension reference
`KEY IN (-1,5)`

is a symbolic reference.

UPSERT creates cells only when all the dimension references in a cell are either positional references to existing dimension values or symbolic references that evaluate to TRUE. No cells are created for any other combination. For example, the dimension references for the first and second rules below will not trigger new cells, those for the third and fourth will.

select group_1, group_2, key_2, m_1, m_2, m_3, m_4 from t model dimension by ( group_1, group_2 ) measures ( key_2 , cast( null as number) m_1 , cast( null as number) m_2 , cast( null as number) m_3 , cast( null as number) m_4 ) rules UPSERT ( m_1[ 'A', GROUP_2 = 'a4' ] = 1 , -- positional, symbolic / existing , FALSE m_2[ 'D', GROUP_2 = 'a3' ] = 2 , -- positional, symbolic / nonexistent, TRUE m_3[ 'A', GROUP_2 = 'a3' ] = 3 , -- positional, symbolic / existing , TRUE m_4[ 'A', ANY ] = 4 -- positional, symbolic / existing , TRUE ) order by group_1 , group_2 ;

GROUP_1 GROUP_2 KEY_2 M_1 M_2 M_3 M_4 ---------- ---------- ----- ---------- ---------- ---------- ---------- A a1 T-1 4 A a2 T-2 4 A a3 T-3 3 4 B a1 T-4 B a2 T-5 B a3 T-6 C a1 T-7 C a2 T-8 a1 T-9 a2 T-10

In rules like the one for M_4 above note that "ANY" is a symbolic reference because it equates to (GROUP_1 IS NOT NULL OR GROUP_1 IS NULL).

#### Gotchas

Given that WHERE clauses are evaluated before MODEL clauses and given that UPSERT may create new cells, be aware that WHERE clauses with FALSE results will not necessarily lead to empty result sets when MODEL is used. Here is a good example of a query some people might expect a "no rows selected" result from, but in fact returns two rows.

select key, key_2, m_1, m_2 from t where 1=2 -- (1) no rows are returned from table T, initial result set is empty model dimension by ( key ) measures ( key_2 , cast( null as number) m_1 , cast( null as number) m_2 ) rules upsert ( M_1[ 0 ] = 1 , -- (2) adds a new row to the result set M_2[ 5 ] = 1 -- (3) adds another new row to the result set ) order by key ;

KEY KEY_2 M_1 M_2 ------ ----- ---------- ---------- 0 1 5 1

In this case, the row with a KEY value of 5 is a new row created by the rule for M_2. It is not related to the row in table T with a KEY value of '5'.