Skip Headers

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

Part Number B10339-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF


The SWITCH command provides a multipath branch in a program. The specific path taken during program execution depends on the value of the control expression that is specified with SWITCH. You can use SWITCH only within programs.


SWITCH control-expression


      CASE case-expression1:

          statement 1.1


          statement 1.n


      CASE case-expression2:

           statement 2.1


           statement 2.n



           statement n.1


           statement n.n





The control-expression argument determines the case label to which program control is transferred by the SWITCH command. When the SWITCH command is executed, control-expression is evaluated and compared with each of the CASE label expressions in the program. When a match is found, control is transferred to that case label. When no match is found, control transfers to the DEFAULT label (if present) or to the statement following the DOEND for SWITCH.

CASE case-expression1, CASE case-expression2, ...

The CASE labels whose expressions (case-expression1, case-expression2, ...) specify the different cases you want to handle. When control-expression matches case-expression, program control is transferred to that CASE label. The CASE label expressions are evaluated at the time the program is run, in the order they appear, until a match is found.

The DEFAULT label is optional. It identifies a special case to which control should be transferred when none of the case-expressions matches the control-expression. When you omit DEFAULT, and no match is found, control is transferred to the statement that follows the DOEND for SWITCH.

All the CASE labels (including DEFAULT) for a SWITCH command must be included within a DO/DOEND bracket immediately following the SWITCH command. Because case-expression is a label, it must be followed by a colon (:). The statements to be executed in a given case must follow the label. Normally, the last statement in a case should be BREAK, which transfers control from SWITCH to the statement that follows the DOEND for SWITCH.

When you omit BREAK (or RETURN, SIGNAL, and so on) at the end of a case, the program will go on to execute the statements for the next case as well. Normally, you do not want this to happen. However, when you plan to execute the same statements for two cases, you can use this to your advantage by placing both CASE labels before the statements.


Control- and Case-Expressions

The SWITCH control-expression can have any data type, as can the case-expressions. The various case-expressions can have different data types. When you specify the name of a dimension (as a literal, non-quoted text expression) as the control-expression or case-expression, Oracle OLAP uses the first value in the dimension's current status list, not the dimension name, as it searches for a match. When the dimension has no values in the status list, Oracle OLAP uses the value NA. An NA control-expression will match the first NA case-expression.

Ampersand Substitution

Avoid using ampersand substitution in a SWITCH control-expression or in a CASE label case-expression. Ampersands will produce unpredictable, and usually undesirable, results.

Multiple SWITCH Commands

You can include more than one SWITCH command in a program. You can also nest SWITCH commands. When a program contains multiple SWITCH commands, each can have its own DEFAULT label, even when the SWITCH commands are nested.

Transferring Control

While BREAK is commonly used to transfer program control within a SWITCH command, it is not the only such statement you can use. You can also use statements such as CONTINUE, GOTO, RETURN, and SIGNAL. Keep in mind that you can use CONTINUE only when the SWITCH command is within a FOR or WHILE loop. See also the entries for these statements and for DO ... DOEND.


Example 23-4 Multipath Branching Using SWITCH in a Program

The following program lines produce one of several types of reports. Before the SWITCH command, the program determines which type of report the user wants and places the value Market or Finance in the variable userchoice. The program switches to the case label that matches that name and produces the report. When the report finishes, the BREAK command transfers control to the cleanup section after the DOEND.

SWITCH userchoice
         CASE 'Market':
         CASE 'Finance':