Skip Headers
Oracle® OLAP DML Reference
11g Release 1 (11.1)

B28126-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

NASKIP2

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.

See also:

$NATRIGGER property, NASKIP option, and NASPELL option.

Data Type

BOOLEAN

Syntax

NASKIP2 = YES|NO

Arguments

YES

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.

NO

(Default) 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.

Notes

Operators in Function Arguments

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 Example 6-71, "Effects of NASKIP and NASKIP2 When an Expression in an Aggregation Function Contains a Negative Values".

How NASKIP2 Works

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

Examples

Example 6-71 Effects of NASKIP and NASKIP2 When an Expression in an Aggregation Function Contains a Negative Values

In the following examples, 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.

  • NASKIP Set to YES, NASKIP2 Set to NO

    In this example, 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
    
  • NASKIP Set to YES, NASKIP2 Set to YES

    In this example, NASKIP is set to YES, which means NA values are ignored by the TOTAL function. NASKIP2 is 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
    
  • NASKIP Set to NO, NASKIP2 Set to YES

    In this example, NASKIP is set to NO, which means that when any values considered by the TOTAL function are NA, TOTAL will return NA. NASKIP2 is 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
    
  • NASKIP Set to NO, NASKIP Set to NO

    In this example, 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 also 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