In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on UPDATE behaviour with cell assignments for missing cells.
Finally, we examine the last and simplest of the three available rule behaviours, UPDATE. Here are the three examples used in the Missing Cells - UPSERT tutorial modified to use UPDATE behaviour. As you can see, missing cells are never created for nonexistent dimension references like '-1', '0', 'D', and 'a4' when UPDATE is specified.
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 UPDATE
(
m_1[ 0 ] = 1 , -- positional reference to nonexistent value
m_2[ 5 ] = 2 , -- positional reference to existing value
m_3[ FOR KEY IN (-1,5) ] = 3 -- positional reference to both
)
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
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 UPDATE
(
m_1[ KEY = 0 ] = 1 , -- symbolic ref. to nonexistent dimension value
m_2[ KEY = 5 ] = 2 , -- symbolic ref. to existing dimension value
m_3[ KEY IN (-1,5) ] = 3 -- symbolic ref. to both
)
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
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 UPDATE
(
m_1['A', GROUP_2='a4'] = 1, -- positional, symbolic / existing, nonexistent
m_2['D', GROUP_2='a3'] = 2, -- positional, symbolic / nonexistent, existing
m_3['A', GROUP_2='a3'] = 3, -- positional, symbolic / existing, existing
m_4['A', ANY ] = 4 -- positional, symbolic / existing, existing
)
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