Skip Headers
Oracle® OLAP DML Reference
10g Release 2 (10.2)

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

Go to previous page
Previous
Go to next page
Next
View PDF

COALESCE

The COALESCE function returns the first non-NA expression in a list of expressions, or NA when all of the expressions evaluate to NA.

Return Value

Data type of the first argument.

Syntax

COALESCE (expr [, expr]...)

Arguments

expr

An expression.

Examples

Example 9-8 Using COALESCE to Determine the Sales Price of a Product

Assume that you have defined the following objects in your analytic workspace. (Note that the sale formula uses the COALESCE function for its calculations.)

DEFINE product_id DIMENSION TEXT
DEFINE supplier_id DIMENSION TEXT
DEFINE list_price VARIABLE DECIMAL <product_id supplier_id>
DEFINE min_price VARIABLE DECIMAL <product_id supplier_id>
 
DEFINE sale FORMULA DECIMAL <Product_id supplier_id>
EQ COALESCE(0.9*list_price, min_price, 5)
 

The following code illustrates limiting supplier_id to a single value and displaying a report that shows the list price, minimum price, and sale price for the products provided by that supplier.

LIMIT supplier_id TO '102050'
REPORT DOWN product_id list_price min_price sale

               ----------SUPPLIER_ID-----------
               -------------102050-------------
PRODUCT_ID     LIST_PRICE MIN_PRICE     SALE
-------------- ---------- ---------- ----------
2382               850.00     731.00     765.00
3355                   NA         NA       5.00
1770                   NA      73.00      73.00
2378               305.00     247.00     274.50
1769                48.00         NA      43.20
1660                16.45      16.45      14.80