|Oracle® OLAP DML Reference
11g Release 2 (11.2)
Part Number E17122-05
The value returned is the same data type as source-expression. When the fill and source expressions do not have the same data type, Oracle OLAP converts the fill expression to the data type of the source expression when possible. Otherwise, an error is produced. When both the source and fill expressions equal
NA, then NAFILL returns
The expression being evaluated. For values of source-expression that do not equal
NA, NAFILL returns the corresponding values of source-expression. Source-expression determines the dimensions and data type of the result.
The expression to be substituted in the return value. Fill-expression must have the same data type as source-expression. Fill-expression is only evaluated for values of source-expression that equal
Functions in the Fill Expression
You can use any functions in the fill expression if they return the same data type as the source expression.
NATRIGGER Takes Precedence Over NAFILL
Oracle OLAP evaluates an $NATRIGGER property expression before applying the NAFILL function. When the $NATRIGGER expression is
NA, then the NAFILL function has an effect.
Example 8-32 Filling NA Values with Zeros
Suppose you have
NA values in the variable
sales and you want to calculate an average that counts those values as zeros. Ordinarily, AVERAGE ignores
NA values and does not count them in the number of values being averaged. You can use NAFILL inside the AVERAGE function to temporarily treat those values as zeros so the NA values count in calculating the average.
REPORT AVERAGE(NAFILL(sales 0.0))