![]() Previous |
![]() Next |
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, which has similar syntax but a different purpose, and which must be used in an Oracle OLAP program. The IF...THEN...ELSE command 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) and the data type of the whole expression is determined using the same rules as those for the binary operators. 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 converts the numeric or text value to a DATE. Also, when the value of one expression is a dimension value then the value of the other expression is converted to a dimension value as it is for QDRs.
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.
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 ...