Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
Syntax
Purpose
The PRESENTNNV
function can be used only in the model_clause
of the SELECT
statement and then only on the right-hand side of a model rule. It returns expr1
when, prior to the execution of the model_clause
, cell_reference
exists and is not null. Otherwise it returns expr2
.
Examples
In the following example, if a row containing sales for the Mouse Pad for the year 2002 exists, and the sales value is not null, then the sales value remains unchanged. If the row exists and the sales value is null, then the sales value is set to 10. If the row does not exist, then the row is created with the sales value set to 10.
SELECT country, prod, year, s FROM sales_view_ref MODEL PARTITION BY (country) DIMENSION BY (prod, year) MEASURES (sale s) IGNORE NAV UNIQUE DIMENSION RULES UPSERT SEQUENTIAL ORDER ( s['Mouse Pad', 2002] = PRESENTNNV(s['Mouse Pad', 2002], s['Mouse Pad', 2002], 10) ) ORDER BY country, prod, year; COUNTRY PROD YEAR S ---------- ----------------------------------- -------- --------- France Mouse Pad 1998 2509.42 France Mouse Pad 1999 3678.69 France Mouse Pad 2000 3000.72 France Mouse Pad 2001 3269.09 France Mouse Pad 2002 10 France Standard Mouse 1998 2390.83 France Standard Mouse 1999 2280.45 France Standard Mouse 2000 1274.31 France Standard Mouse 2001 2164.54 Germany Mouse Pad 1998 5827.87 Germany Mouse Pad 1999 8346.44 Germany Mouse Pad 2000 7375.46 Germany Mouse Pad 2001 9535.08 Germany Mouse Pad 2002 10 Germany Standard Mouse 1998 7116.11 Germany Standard Mouse 1999 6263.14 Germany Standard Mouse 2000 2637.31 Germany Standard Mouse 2001 6456.13 18 rows selected.
The preceding example requires the view sales_view_ref
. Please refer to "Examples" to create this view.