| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
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. An 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
A number of options and functions control how NA values are treated. For example:
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
| Statement | Description |
|---|---|
| NASKIP |
An option that controls whether NA values are considered as input to aggregation functions. |
| NASKIP2 |
An option that controls how NA values are treated in arithmetic operations with the + (plus) and - (minus) operators. |
| NASPELL |
An option that controls the spelling that is used for NA values in output. |
| RECURSIVE |
An option that controls the ability of a formula or $NATRIGGER expression to call itself. |
| TRIGGERMAXDEPTH |
An option that specifies the maximum number of $NATRIGGER property expressions that Oracle OLAP can execute simultaneously. |
| TRIGGERSTOREOK |
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
| Property | Description |
|---|---|
| $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). |
| $STORETRIGGERVAL |
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 YES.
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 YES, and NA values are ignored by aggregation functions. Only expressions with actual values are used in calculations.
When you set the NASKIP option to NO, then 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 NA.
When NASKIP is NO and any value in the range is NA, then MOVINGMAX returns NA.
The NASKIP2 option controls how NA values are treated in arithmetic operations with the addition (+) and subtraction (-) operators.
By default, the value of the NASKIP2 option is NO. 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 NA.
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 NA.
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.
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 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)
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|
