|Oracle9i OLAP Services Developer's Guide to the OLAP DML
Release 1 (9.0.1)
Part Number A86720-01
Working with Expressions, 14 of 14
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.
NA is the value of any cell to which a specific data value has not been assigned or for which data cannot be calculated. An NA value has no specific data type.
Certain functions (for example, the aggregation functions) return an NA value 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.
To set the value of a variable or relation to NA, you can use the = command, as shown in the following example.
If SALES is a dimensioned variable, then the = command loops through all of the values of SALES, setting them to NA.
The following options and functions control how NA values are treated in expressions:
An NATRIGGER property expression is evaluated before applying the NAFILL function or the NASKIP, NASKIP2, or NASPELL options. If 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:
The ROLLUP and AGGREGATE commands and the AGGREGATE function ignore the NATRIGGER property setting for a variable during a rollup or aggregation operation. Additionally, when an EXPORT (EIF file or pipeline) command is executed, the NATRIGGER property expression on a variable is not evaluated when the variable is simply exported; the NATRIGGER property expression is only evaluated if 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.
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.
The NASKIP2 option controls how NA values are treated in arithmetic operations with the addition (+) and subtraction (-) operators.
2+NAresults in NA.
NA-NAboth result in NA.
NASKIP and NASKIP2 do not change your data. They only affect the results of calculations on your data. If you would prefer a more targeted influence on any kinds of expressions, not just functions or addition (+) and subtraction (-) operations, and also have 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 assignment statements, you can use NAFILL to make a permanent substitution for NAs in your data.
NAFILL returns the value of a specified expression unless its value is NA, in which case NAFILL returns the substitute value you specify.
The following command uses NAFILL, but does not change the data in storage. It merely fetches the data in the SALES variable with each NA replaced with the number 1.
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.
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.