Skip Headers

Oracle® OLAP DML Reference
10g Release 1 (10.1)

Part Number B10339-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
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 source-expression.


NAFILL(source-expression fill-expression)



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 NA.


Mismatched Data Types

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.

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.

NA Fill Expression

When both the source and fill expressions equal NA, then NAFILL returns NA.

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 18-11 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.