Conditional Expressions

A conditional expression is an expression you can use to select between values based on a condition. You can use conditional expression as part of any other expression as long as the data type is appropriate. Oracle OLAP supports the use of the following conditional expressions:

IF...THEN...ELSE Expressions
SWITCH Expressions

IF...THEN...ELSE Expressions

An IF expression is an expression you can use to select one of two values based on a Boolean condition.

Note:

Do not confuse the IF expression with the IF...THEN...ELSE Command statement, which has similar syntax but a different purpose, and which must be used in an Oracle OLAP program. The IF statement does not have a data type and is not evaluated like an expression.

An IF expression has the following syntax.

IF Boolean-expression THEN expression1 ELSE expression2

In most cases, expression1 and expression2 must be of the same basic data type (numeric, text, or Boolean). The value of the whole expression is the value of either expression1 or expression2. However, when the data type of either expression1 or expression2 is DATE, it is possible for the other expression to have a numeric or text data type. Because Oracle OLAP expects both data types to be DATE, it will convert the numeric or text value to a DATE.

You can nest IF expressions; however, in this case, you might want to use a SWITCH expression instead as discussed in "SWITCH Expressions".

An IF expression is processed by first evaluating the Boolean expression; then:

When the result of the Boolean expression is TRUE, then expression1 is evaluated and returns that value.
When the result of the Boolean expression is FALSE, then expression2 is evaluated and returns that value.

The expression1 and expression2 arguments are any valid OLAP DML expressions that evaluate to the same basic data type. However, when the data type of either value is DATE, it is possible for the other value to have a numeric or text data type. Because both data types are expected to be DATE, Oracle OLAP converts the numeric or text value to a DATE. The data type of the whole expression is the same as the two expressions. When the result of the Boolean expression is NA, then NA is returned.

Example 3-2 Using an IF Expression

This example shows a sales bonus report. The bonus is 5 percent of the amount that sales exceeded budget, but when sales in the district are below budget, then the bonus is zero.

LIMIT month TO 'Jan02' TO 'Jun02'
LIMIT product TO 'Tents'
REPORT DOWN district IF sales-sales.plan LT 0 THEN 0 
       ELSE .05*(sales-sales.plan)

PRODUCT: TENTS
        ---IF SALES-SALES.PLAN LT 0 THEN 0 ELSE .05*(SALES-SALES.PLAN)---
          ----------------------MONTH------------------------------
DISTRICT   Jan02    Feb02    Mar02     Apr02    May02    Jun02
--------- -------- -------- -------- ------- --------- ----------
Boston      229.53     0.00     0.00    0.00    584.51     749.13
Atlanta       0.00     0.00     0.00  190.34    837.62   1,154.87
Chicago       0.00     0.00     0.00   84.06    504.95     786.81
...

SWITCH Expressions

A SWITCH expression consists of a series of CASE expressions. You can use a SWITCH expression as an alternative to a complicated, nested IF ... THEN ... ELSE expression when all the conditions are equality comparisons with a single value.

Note:

Do not confuse the SWTICH expression with the SWITCH command statement, which has similar syntax but a different purpose, and which must be used in an Oracle OLAP program. The SWITCH command statement is not evaluated like an expression.

A SWITCH expression has the following syntax.

SWITCH expression DO { case-label ... exp [,] } ... DOEND

where case-label has the following syntax:

CASE exp: | DEFAULT:

When processing a SWITCH expression, Oracle OLAP compares each CASE expression in succession until it finds a match. When a match is found, it returns the value specified after the last label of the current case group. When no match is found and a DEFAULT label is specified, it returns the value specified for the DEFAULT case; otherwise it returns NA.

Example 3-3 Using a SWITCH Expression Instead of an IF Expression

Assume that you have coded the following OLAP DML statement which includes nested IF...THEN...ELSE statements.

testprogram = IF testtype EQ 0 -
                THEN 'program0' -
                ELSE IF testtype EQ 1 -
                  THEN 'program1' -
                  ELSE IF testtype EQ 2 OR testtype EQ 3 -
                    THEN 'program2'
                    ELSE NA

You could, instead, code the same behavior using a SWITCH expression as shown below.

testprogram = SWITCH testtype DO -
                CASE 0: 'program0', -
                CASE 1: 'program1', -
                CASE 2: -
                CASE 3: 'program2', -
                DEFAULT: NA -
                DOEND

You could also code the same behavior using a SWITCH statement that spans fewer lines, omits commas, and omits the DEFAULT case since NA is the default return value when a match is not found.

testprogram = SWITCH testtype DO CASE 0: 'program0' CASE 1: 'program1' -
              CASE 2: CASE 3: 'program2' DOEND