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 11-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
```