Oracle® OLAP DML Reference 11g Release 1 (11.1) Part Number B2812601 


View PDF 
The NAFILL function returns the values of the source expression with any NA
values replaced with the specified fill expression.
Return Value
The value returned is the same data type as sourceexpression. 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 NA
.
Syntax
NAFILL(sourceexpression fillexpression)
Arguments
The expression being evaluated. For values of sourceexpression that do not equal NA
, NAFILL returns the corresponding values of sourceexpression. Sourceexpression determines the dimensions and data type of the result.
The expression to be substituted in the return value. Fillexpression must have the same data type as sourceexpression. Fillexpression is only evaluated for values of sourceexpression that equal NA
.
Notes
Functions in the Fill Expression
You can use any functions in the fill expression as long as 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.
Examples
Example 832 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 they will count in calculating the average.
REPORT AVERAGE(NAFILL(sales 0.0))