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 7-45 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