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 a SWITCH statement only within programs.
Note:
Do not confuse the use of a single SWITCH command with the use of SWTICH as a conditional operator in an expression. See "SWITCH Expressions".SWITCH control-expression
DO
CASE case-expression1:
statement 1.1
...
statement 1.n
BREAK
CASE case-expression2:
statement 2.1
...
statement 2.n
BREAK
[DEFAULT:
statement n.1
...
statement n.n
BREAK]
DOEND
The control-expression argument determines the case label to which program control is transferred by the SWITCH statement. When the SWITCH statement 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.
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 statement must be included within a DO/DOEND bracket immediately following the SWITCH statement. 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 goes 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.
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 matches the first NA case-expression.
Do Not Use Ampersand Substitution with SWITCH
Avoid using ampersand substitution in a SWITCH control-expression or in a CASE label case-expression. Ampersands produce unpredictable, and usually undesirable, results.
You can include multiple SWITCH statements 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.
While BREAK is commonly used to transfer program control within a SWITCH statement, 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 statement is within a FOR or WHILE loop. See also the entries for these statements and for DO ... DOEND.
Example 10-153 Multipath Branching Using SWITCH in a Program
The following program lines produce one of several types of reports. Before the SWITCH statement, 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 statement transfers control to the cleanup section after the DOEND.
SWITCH userchoice
DO
CASE 'Market':
...
BREAK
CASE 'Finance':
...
BREAK
DEFAULT:
...
BREAK
DOEND
cleanup:
...