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 IF...THEN...ELSE command executes one or more commands in a program when a specified condition is met. Optionally, it also executes an alternative command or group of commands when the condition is not met. You can use IF only within programs.

You can also use IF as a conditional operator in an expression. See "IF as a Conditional Operator".


IF boolean-expression

   THEN statement1

   [ELSE statement2]



Any valid Boolean expression that returns either TRUE or FALSE.

THEN statement1

Oracle OLAP executes the statement1 argument when the Boolean expression is TRUE. The statement1 must be on the same line as THEN.

ELSE statement2

Oracle OLAP executes the statement2 argument when the Boolean expression is FALSE. The statement2 must be on the same line as ELSE. When you omit the ELSE phrase, execution continues with the statement after the whole IF...THEN... command in the program.


IF with DO

You can use the IF command for conditional execution of two or more statements by following the THEN or ELSE (or both) keywords with a DO ... DOEND sequence. See Example 14-13, "Using IF...THEN...ELSE".

IF as a Conditional Operator

When you use IF as a conditional operator in an expression, it has the following format. ELSE is required when IF is used as an operator.

IF boolean-exp THEN exp1 ELSE exp2

In most cases, exp1 and exp2 must be of the same basic data type (numeric, text, or Boolean). The value of the whole expression is the value of either exp1 or exp2.

However, when the data type of either exp1 or exp2 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.

Single or Multiple Lines

When IF is used as an expression, the THEN and ELSE keywords must be on the same line as IF. When used as a command, THEN and ELSE must be on separate lines.


Example 14-13 Using IF...THEN...ELSE

The following lines from a program illustrate the use of IF...THEN...ELSE.... When the Boolean expression ANY(DOLLARS LT 200000) is TRUE, the statements following THEN (statement group 1) are executed. When the expression is FALSE, the statements following ELSE (statement group 2) are executed instead.

  ... " (statement group 1)
  ... "(statement group 2)

Example 14-14 Using IF as a Conditional Operator

In a program that produces a report, you would like to report a previous year's actual expenses or the current year's budget, depending on the year passed to the program as an argument. A conditional expression in a JOINCHARS function produces a heading with the word Actual or Budget. Another conditional expression selects the variable to report. The program would include the following lines.

ARGUMENT cur.year year
LIMIT month TO year cur.year
   HEADING JOINCHARS( 'Expenses: ' -
                        IF cur.year LT 'Yr95' -
                        THEN 'Actual FOR ' -
                        ELSE 'Budget FOR ', -
                       cur.year ) -
   IF cur.year LT 'Yr95' THEN actual ELSE budget