In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on cell references for missing cells on the right hand side of a rule.
By default, if a cell reference on the right hand side of a rule targets a cell that does not exist, the reference evaluates to NULL. In the following snippet dimension "0" does not exist in the result set.
set null "(null)"
select
key, num_measure, text_measure, date_measure
from
t
where
key = 1
model
dimension by ( key )
measures
(
num_val ,
group_1 ,
date_val ,
123 as num_measure ,
'abcdefghijklmn' as text_measure ,
sysdate as date_measure
)
rules upsert
(
num_measure [1] = num_val [0] ,
text_measure[1] = group_1 [0] ,
date_measure[1] = date_val[0]
)
;
KEY NUM_MEASURE TEXT_MEASURE DATE_MEASU
------ ----------- -------------- ----------
1 (null) (null) (null)
Note that missing cell references are relative to the query results, not the base table.
For example, in the following snippets dimension reference
[2]
points to an existing cell in the first snippet and a missing cell in the second.
select
key, num_measure, text_measure, date_measure
from
t
where
KEY = 2
model
dimension by ( key )
measures
(
num_val ,
group_1 ,
date_val ,
123 as num_measure ,
'abcdefghijklmn' as text_measure ,
sysdate as date_measure
)
rules upsert
(
num_measure [any] = num_val [2] ,
text_measure[any] = group_1 [2] ,
date_measure[any] = date_val[2]
)
;
KEY NUM_MEASURE TEXT_MEASURE DATE_MEASU
------ ----------- -------------- ----------
2 200 A 2005-06-12
select
key, num_measure, text_measure, date_measure
from
t
where
KEY = 1 -- row with KEY = '2' is not included this time
model
dimension by ( key )
measures
(
num_val ,
group_1 ,
date_val ,
123 as num_measure ,
'abcdefghijklmn' as text_measure ,
sysdate as date_measure
)
rules upsert
(
num_measure [any] = num_val [2] ,
text_measure[any] = group_1 [2] ,
date_measure[any] = date_val[2]
)
;
KEY NUM_MEASURE TEXT_MEASURE DATE_MEASU
------ ----------- -------------- ----------
1 (null) (null) (null)
IGNORE NAV
You can control missing cell behaviour with the IGNORE NAV option which causes Oracle to return
- 0 for missing numeric data
- an empty string for missing character/string data
- 01-JAN-2000 for missing date data
- NULL for all other data types.
alter session set nls_date_format = 'YYYY-MM-DD';
select
key, num_measure, text_measure, date_measure
from
t
where
key = 1
model
IGNORE NAV
dimension by ( key )
measures
(
num_val ,
group_1 ,
date_val ,
123 as num_measure ,
'abcdefghij' as text_measure ,
sysdate as date_measure
)
rules upsert
( num_measure [1] = num_val [0] ,
text_measure[1] = group_1 [0] ,
date_measure[1] = date_val[0]
)
;
KEY NUM_MEASURE TEXT_MEASU DATE_MEASU
------ ----------- ---------- ----------
1 0 (null) 2000-01-01
Note that in Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) - Chapter 22 - Treatment of NULLs and Missing Cells it incorrectly states that "01-JAN-2001" is returned for missing data data. The correct value, "01-JAN-2000", is documented in Oracle® Database SQL Reference 10g Release 2 (10.2) - SELECT - model_clause.
IS PRESENT, PRESENTV, and PRESENTNNV
If you need to distinguish between NULL values from your data and NULL values representing missing cells you can use the IS PRESENT condition or the PRESENTV and PRESENTNNV functions on the right hand side of a rule.
Here is an example of their use. Note that cell references with a dimension of "0" point to cells that were missing from the result set before the MODEL clause was executed.
select
key, num_val, num_val_is_present, presentv_num_val, presentnnv_num_val
from
t
where
key in ( 1, 4 )
model
dimension by ( key )
measures
(
num_val ,
cast ( 'default' as varchar2(20) ) as num_val_is_present ,
cast ( 'default' as varchar2(20) ) as presentv_num_val ,
cast ( 'default' as varchar2(20) ) as presentnnv_num_val
)
rules upsert
(
num_val_is_present[0] =
case when num_val[cv()] IS PRESENT then 'TRUE' else 'FALSE' end ,
num_val_is_present[1] =
case when num_val[cv()] IS PRESENT then 'TRUE' else 'FALSE' end ,
num_val_is_present[4] =
case when num_val[cv()] IS PRESENT then 'TRUE' else 'FALSE' end ,
--
presentv_num_val [0] = PRESENTV (num_val[cv()], 'expr 1', 'expr 2'),
presentv_num_val [1] = PRESENTV (num_val[cv()], 'expr 1', 'expr 2'),
presentv_num_val [4] = PRESENTV (num_val[cv()], 'expr 1', 'expr 2'),
--
presentnnv_num_val[0] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
presentnnv_num_val[1] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
presentnnv_num_val[4] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2')
)
order by
key
;
KEY NUM_VAL NUM_VAL_IS_PRESENT PRESENTV_NUM_VAL PRESENTNNV_NUM_VAL
------ ------- -------------------- -------------------- --------------------
0 (null) FALSE expr 2 expr 2
1 100 TRUE expr 1 expr 1
4 (null) TRUE expr 1 expr 2
It is important to note that these three functions consider a cell missing if it does not exist before the MODEL clause is executed. They will return the same results as above even when the cell they reference is created, via a rule, before the functions are called. The following variation of the last snippet demonstrates this.
select
key, num_val, num_val_is_present, presentv_num_val, presentnnv_num_val
from
t
where
key in ( 1, 4 )
model
dimension by ( key )
measures
(
num_val ,
cast ( 'default' as varchar2(20) ) as num_val_is_present ,
cast ( 'default' as varchar2(20) ) as presentv_num_val ,
cast ( 'default' as varchar2(20) ) as presentnnv_num_val
)
rules upsert
(
num_val[0] = 0 , --(1)
--
num_val_is_present[0] =
case when num_val[cv()] IS PRESENT then 'TRUE' else 'FALSE' end , --(2)
num_val_is_present[1] =
case when num_val[cv()] IS PRESENT then 'TRUE' else 'FALSE' end ,
num_val_is_present[4] =
case when num_val[cv()] IS PRESENT then 'TRUE' else 'FALSE' end ,
--
presentv_num_val [0] = PRESENTV (num_val[cv()], 'expr 1', 'expr 2'), --(3)
presentv_num_val [1] = PRESENTV (num_val[cv()], 'expr 1', 'expr 2'),
presentv_num_val [4] = PRESENTV (num_val[cv()], 'expr 1', 'expr 2'),
--
presentnnv_num_val[0] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'), --(4)
presentnnv_num_val[1] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
presentnnv_num_val[4] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2')
)
order by
key
--
-- (1) num_val[0] is created
-- (2) still yields FALSE
-- (3) still yields 'expr 2'
-- (4) still yields 'expr 2'
;
KEY NUM_VAL NUM_VAL_IS_PRESENT PRESENTV_NUM_VAL PRESENTNNV_NUM_VAL
------ ------- -------------------- -------------------- --------------------
0 0 FALSE expr 2 expr 2
1 100 TRUE expr 1 expr 1
4 (null) TRUE expr 1 expr 2
Other SQL functions, such as PRESENTNNV's close cousin NVL2, operate on cells as they appear at the time the function is called. The following two snippets demonstrate the difference between PRESENTNNV and NVL2. 1
In this first snippet the results are the same for both functions.
select
key, num_val, presentnnv_num_val, nvl2_num_val
from
t
where
key in ( 1, 4 )
model
dimension by ( key )
measures
(
num_val ,
cast ( 'default' as varchar2(20) ) as num_val_is_present ,
cast ( 'default' as varchar2(20) ) as presentv_num_val ,
cast ( 'default' as varchar2(20) ) as presentnnv_num_val ,
cast ( 'default' as varchar2(20) ) as nvl2_num_val
)
rules upsert
(
presentnnv_num_val[0] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
presentnnv_num_val[1] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
presentnnv_num_val[4] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
--
nvl2_num_val [0] = NVL2 (num_val[cv()], 'expr 1', 'expr 2'),
nvl2_num_val [1] = NVL2 (num_val[cv()], 'expr 1', 'expr 2'),
nvl2_num_val [4] = NVL2 (num_val[cv()], 'expr 1', 'expr 2')
)
order by
key
;
KEY NUM_VAL PRESENTNNV_NUM_VAL NVL2_NUM_VAL
------ ------- -------------------- --------------------
0 (null) expr 2 expr 2
1 100 expr 1 expr 1
4 (null) expr 2 expr 2
In this next snippet the first rule creates cell "NUM_VAL[0]". The results for NVL2 now differ from those of PRESENTNNV.
select
key, num_val, presentnnv_num_val, nvl2_num_val
from
t
where
key in ( 1, 4 )
model
dimension by ( key )
measures
(
num_val ,
cast ( 'default' as varchar2(20) ) as presentnnv_num_val ,
cast ( 'default' as varchar2(20) ) as nvl2_num_val
)
rules upsert
(
num_val[0] = 0 , --(1)
--
presentnnv_num_val[0] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'), --(2)
presentnnv_num_val[1] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
presentnnv_num_val[4] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
--
nvl2_num_val [0] = NVL2 (num_val[cv()], 'expr 1', 'expr 2'), --(3)
nvl2_num_val [1] = NVL2 (num_val[cv()], 'expr 1', 'expr 2'),
nvl2_num_val [4] = NVL2 (num_val[cv()], 'expr 1', 'expr 2')
)
order by
key
--
-- (1) num_val[0] is created
-- (2) still yields 'expr 2'
-- (3) yields 'expr 1' this time
;
KEY NUM_VAL PRESENTNNV_NUM_VAL NVL2_NUM_VAL
------ ------- -------------------- --------------------
0 0 expr 2 expr 1
1 100 expr 1 expr 1
4 (null) expr 2 expr 2
Acknowledgements
- Re: PRESENTNNV vs NVL2 by OTN user martyn