MODEL Clause

Missing Cells - UPDATE

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
```

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

• [url=http://www.sqlsnippets.com/en/topic-11780.html]SQL Snippets: MODEL Clause - Missing Cells - UPDATE[/url]

• <a href="http://www.sqlsnippets.com/en/topic-11780.html">SQL Snippets: MODEL Clause - Missing Cells - UPDATE</a>