Previous
Previous
 
Next
Next

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, which has similar syntax but a different purpose, and which must be used in an Oracle OLAP program. The SWITCH command 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.

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