Skip Headers

Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)

Part Number A95298-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

Working with Expressions, 9 of 11


Conditional Expressions

A conditional expression is an expression you can use to select one of two values based on a Boolean condition. A conditional expression contains the conditional operator IF. . .THEN. . .ELSE and has the following format.

IF Boolean-expression THEN expression1 ELSE expression2

You can use a conditional expression as part of any other expression as long as the data type is appropriate.



Note:

Do not confuse a conditional expression with the IF command, which has similar syntax but a different purpose. The IF command does not have a data type and is not evaluated like an expression.


A conditional expression is processed by first evaluating the Boolean expression; then:

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, it will convert the numeric or text value to a DATE. The data type of the whole expression is the same as the two expressions.

If the result of the Boolean expression is NA, then NA is returned.

Example 4-4 Report with Conditional Expression

This example shows a sales bonus report. The bonus is 5 percent of the amount that sales exceeded budget, but if 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
.
.
.

Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 2001, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback