|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
At any given time, some of the cells of an analytic workspace data object may be empty. An empty cell occurs when a specific data value has not been assigned to it or when a data value cannot be calculated for the cell. The value of any empty cell in an object is
NA value has no specific data type. Certain functions (for example, the aggregation functions) return an
NA values when the information that is requested with the function is not available or cannot be calculated. Similarly, an expression whose value cannot be calculated has
NA as its value.
There are cases in which you might specify an operation for which no data is available. For example, there might be no appropriate value for a given cell in a variable, for the return value of a function, or for the value of an expression that includes an arithmetic operator. In these cases, an
NA (Not Available) value is automatically supplied.
To set the values of a variable or relation to
NA, you can use an assignment statement (SET), as shown in the following example.
sales = NA
The options listed in Table 3-5, "NA Value Options ".
The NAFILL function returns the values of the source expression with any
NA values appearing as the specified fill expression. You can include this function in an expression to control the format of its value.
System properties listed in Table 3-6, "System Properties Used When Working with NA Values".
Table 3-5 NA Value Options
||An option that controls whether NA values are considered as input to aggregation functions.|
||An option that controls how NA values are treated in arithmetic operations with the + (plus) and - (minus) operators.|
||An option that controls the spelling that is used for NA values in output.|
||An option that controls the ability of a formula or $NATRIGGER expression to call itself.|
||An option that specifies the maximum number of $NATRIGGER property expressions that Oracle OLAP can execute simultaneously.|
||An option that determines whether Oracle OLAP permanently replaces NA values in the cells of a variable with the value of the $NATRIGGER property expression that is set for the variable.|
Table 3-6 System Properties Used When Working with NA Values
|$NATRIGGER||A property that specifies values to substitute for NA values that are in the object, but not in the session cache for the object (if any).|
||A property that specifies that NA values in an object be permanently replaced by the values specified by the $NATRIGGER property.|
|$VARCACHE||A property that specifies whether Oracle OLAP stores or caches variable data that is the result of the execution of a AGGREGATE function or $NATRIGGER expression.|
An $NATRIGGER property expression is evaluated before applying the NAFILL function or the NASKIP, NASKIP2, or NASPELL options. When the $NATRIGGER expression is NA, then the NAFILL function and the NA options have an effect. Additionally, the $NATRIGGER property allows you a good deal of flexibility about handling NA values:
You can make $NATRIGGERs recursive or mutually recursive by including triggered objects within the value expression. You must set the RECURSIVE option to
YES before a formula, program, or other $NATRIGGER expression can invoke a trigger expression again while it is executing. For limiting the number of triggers that can execute simultaneously, see the TRIGGERMAXDEPTH option.
You can replace the NA value in the cells of the variable with the $NATRIGGER expression value by setting the TRIGGERSTOREOK option to
YES and setting the $STORETRIGGERVAL property on the variable to
The ROLLUP and AGGREGATE commands and the AGGREGATE function ignore the $NATRIGGER property setting for a variable during a rollup or aggregation operation. Additionally, the $NATRIGGER property expression on a variable is not evaluated when the variable is simply exported with an EXPORT TO EIF file command. The $NATRIGGER property expression is only evaluated when the variable is part of an expression that is calculated during the export operation.
The NASKIP option controls how
NA values are treated in aggregation functions:
By default, the NASKIP option is set to
NA values are ignored by aggregation functions. Only expressions with actual values are used in calculations.
When you set the NASKIP option to
NA values are considered as input to aggregation functions. When any of the values being considered are
NA, then the function returns
NA for that value.
Setting NASKIP to
NO is useful for cases in which having
NA values in the data makes the calculation itself invalid. For example, when you use the MOVINGMAX function, you specify a range from which to select the maximum value.
When NASKIP is
YES (the default), then MOVINGMAX returns
NA only when all the values in the range are
When NASKIP is
NO and any value in the range is
NA, then MOVINGMAX returns
The NASKIP2 option controls how
NA values are treated in arithmetic operations with the addition (
+) and subtraction (
By default, the value of the NASKIP2 option is
NA values are treated as NAs in arithmetic operations using the addition (
+) and subtraction (
-) operators. When any of the operands being considered is
NA, then the arithmetic operation evaluates to
NA. For example, by default,
2+NA results in
When you set the value of the NASKIP2 option to
YES, then zeroes are substituted for
NA values in arithmetic operations using the addition (
+) and subtraction (
-) operators. The two special cases of
NA+ NA and
NA-NA both result in
NASKIP and NASKIP2 do not change your data. They only affect the results of calculations on your data. When you would prefer a more targeted influence on any kind of expressions, and want the option of making an actual change in your data, then you can use the NAFILL function.
The effect of the NAFILL function is limited to the single expression you specify. It can be any kind of expression, not just a function or an addition (
+) or subtraction (
-) operation. In addition, you can use NAFILL to substitute anything for the
NAs in the expression, not just zeroes. Moreover, using an assignment statement (SET), you can use NAFILL to make a permanent substitution for NAs in your data.
The following command uses NAFILL to replace the
NA values in the
sales variable with the number
1 and then assign those values to the variable. This makes the substitution permanent in your data.
sales = NAFILL(sales, 1)
The following command illustrates the use of NAFILL for more specialized purposes. By substituting zeros for
NA values, NAFILL in this example forces the AVERAGE function to include
NA values when it counts the number of values it is averaging. The substitution is temporary, lasting only for the duration of this command.z
SHOW AVERAGE(NAFILL(sales 0.0) district)