COALESCE

Purpose

This function returns the first non-null expression in the list of parameters. At least two parameters must be specified.

Syntax

COALESCE (expr [, expr]...)

Parameters

expr can be a value or expression of a numeric data type (NUMBER, FLOAT, BINARY_FLOAT, or BINARY_DOUBLE) or a character data type (CHAR, VARCHAR2, NCHAR, or NVARCHAR2).

Return type

• The return type is the same as the data type of the first non-null expression expr in the parameter list.

• If all the parameters are NULL, the function returns NULL.

Examples

Create a table tbl1 with the following columns: product_id for the product ID, list_price for the original price, min_price for the lowest price, and Sale for the actual selling price. Set the discount to 80% and calculate the actual selling price for each product. In this case, you can use the COALESCE function. If list_price is empty, calculate the selling price based on the lowest price min_price. If min_price is also empty, calculate the selling price based on 10.

obclient> CREATE TABLE tbl1(supplier_id INT, product_id INT,list_price NUMERIC,min_price NUMERIC);
Query OK, 0 rows affected

obclient> INSERT INTO tbl1 VALUES ('102050', '1659', '45', NULL),('102050', '1770', NULL, '70'),
    ('102050', '2370', '305', '247'),('102050', '2380', '750', '731'),('102050', '3255', NULL, NULL);
Query OK, 5 rows affected
Records: 5  Duplicates: 0  Warnings: 0

obclient> SELECT product_id, list_price,min_price,COALESCE(0.8*list_price, min_price, 10) "Sale" FROM tbl1;
+------------+------------+-----------+------+
| PRODUCT_ID | LIST_PRICE | MIN_PRICE | Sale |
+------------+------------+-----------+------+
|       1659 |         45 |      NULL |   36 |
|       1770 |       NULL |        70 |   70 |
|       2370 |        305 |       247 |  244 |
|       2380 |        750 |       731 |  600 |
|       3255 |       NULL |      NULL |   10 |
+------------+------------+-----------+------+
5 rows in set