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


View PDF 
The NASKIP2 option controls how NA
values are treated in arithmetic operations with the +
(plus) and 
(minus) operators. The result is NA
when any operand is NA
unless NASKIP2 is set to YES
.
Data type
BOOLEAN
Syntax
NASKIP2 = YESNO
Arguments
Zeroes are substituted for NA
values in arithmetic operations using the +
(plus) and 
(minus) operators. The two special cases of NA
+
NA
and NA

NA
both result in NA
.
NA
values are treated as NAs in arithmetic operations using the +
(plus) and 
(minus) operators. When any of the operands being considered is NA
, the arithmetic operation evaluates to NA
. (Default)
Notes
NASKIP2 is independent of NASKIP. NASKIP2 applies only to arithmetic operations with the +
(plus) and 
(minus) operators. NASKIP applies only to aggregation functions. However, when an expression argument to an aggregation function contains a+
(plus) and 
(minus) operator, the results of the calculation depend on both NASKIP and NASKIP2. See "The Effects of NASKIP2 and NASKIP".
The following four lines show four steps in the evaluation of a complex expression that contains NAs when NASKIP2 is set to YES
.
3 * (NA + NA)  5 * (NA + 3) 3 * NA  5 * 3 NA  15 15
An $NATRIGGER property expression is evaluated before the NAFILL function or the NASKIP, NASKIP2, or NASPELL option is applied. When the $NATRIGGER expression is NA
, the NAFILL function and the NA
options have an effect.
Examples
Example 1816 The Effects of NASKIP2 and NASKIP
In the following example, INTEGER variables X
and Z
, dimensioned by the INTEGER dimension INTDIM, have the values shown in the second and third columns of the report. The sum of X + Z is given for each combination of NASKIP and NASKIP2 settings, starting with their defaults. The example also shows that when the +
(plus) operator is used in the expression argument to the TOTAL function, the results that are returned by TOTAL depend on the settings of both NASKIP and NASKIP2.
Example 1817 NASKIP Set to YES, NASKIP2 Set to NO
In the first set of examples, NASKIP is set to YES
, which means NA
values are ignored by the TOTAL function. NASKIP2 is set to NO
, which means that the result of a +
(plus) operation will be NA
when any of the operands are NA
.
NASKIP = YES NASKIP2 = NO COLWIDTH = 5 REPORT LEFT W 6 DOWN intdim x, z, x + z
These statements produce the following output. With NASKIP2 set to NO
, the expression X + Z evaluates to NA
when either X or Z is NA
.
INTDIM X Z x + z     1 NA 2 NA 2 3 NA NA 3 7 6 13
The following statement uses a +
(plus) operator within the expression argument to the TOTAL function.
SHOW TOTAL(x + z)
This statement produces the following result.
13
The next statement uses the +
(plus) operator to add the results that are returned by two TOTAL functions.
SHOW TOTAL(x) + TOTAL(z)
This statement produces the following result.
18
Example 1818 NASKIP Set to YES, NASKIP2 Set to YES
In the next set of examples, NASKIP is again set to YES
, which means NA
values are ignored by the TOTAL function. NASKIP2 is now set to YES
, which means that NA
values are ignored by the +
(plus) operator
NASKIP = YES NASKIP2 = YES REPORT LEFT W 6 DOWN intdim x, z, x + z
These statements produce the following output. With NASKIP2 set to YES
, NA
values are ignored when the expression X
+
Z
is evaluated.
INTDIM X Z X + Z     1 NA 2 2 2 3 NA 3 3 7 6 13
The following statement uses a +
(plus) operator within the expression argument to the TOTAL function.
SHOW TOTAL(x + z)
This statement produces the following result.
18
The next statement uses the +
(plus) operator to add the results that are returned by two TOTAL functions.
SHOW TOTAL(x) + TOTAL(z)
This statement produces the following result.
18
Example 1819 NASKIP Set to NO, NASKIP2 Set to YES
In the next set of examples, NASKIP is set to NO
, which means that when any values considered by the TOTAL function are NA
, TOTAL will return NA
. NASKIP2 is again set to YES
, which means that NA
values are ignored by the +
(plus) operator.
NASKIP = NO NASKIP2 = YES REPORT LEFT W 6 DOWN intdim x, z, x + z
These statements produce the following result.
INTDIM X Z X + Z     1 NA 2 2 2 3 NA 3 3 7 6 13
The following statement uses a +
(plus) operator within the expression argument to the TOTAL function.
SHOW TOTAL(x + z)
This statement produces the following result.
18
The next statement uses the +
(plus) operator to add the results that are returned by two TOTAL functions.
SHOW TOTAL(x) + TOTAL(z)
This statement produces the following result.
NA
Example 1820 NASKIP Set to NO, NASKIP Set to NO
In the final set of examples, NASKIP is again set to NO
, which means that when any values considered by the TOTAL function are NA
, TOTAL will return NA
. NASKIP2 is now set to NO
, which means that the result of a +
(plus) operation will be NA
when any of the operands are NA
.
NASKIP = NO NASKIP2 = NO REPORT LEFT W 6 DOWN intdim x, z, x + z
These statements produce the following result.
INTDIM X Z X + Z     1 NA 2 NA 2 3 NA NA 3 7 6 13
The following statement uses a +
(plus) operator within the expression argument to the TOTAL function.
SHOW TOTAL(x + z)
This statement produces the following result.
NA
The next statement uses the +
(plus) operator to add the results that are returned by two TOTAL functions.
SHOW TOTAL(x) + TOTAL(z)
This statement produces the following result.
NA