COALESCE

Purpose

You can call this function to return the first non-null expression in the parameter list that contains at least two parameters.

Syntax

COALESCE (expr [, expr]...)

Parameters

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

Note

The values in the parameter list must be of consistent data types, except for non-null expressions.

Return types

• This function returns the same value type as the first non-null expr.

• If all parameters are NULL, NULL is returned.

Examples

Create the tbl1 table, wherein product_id is the product ID, list_price is the original price of a product, min_price is the lowest price of a product, and Sale is the actual selling price of a product. Set the price discount to 80%, and calculate the actual selling price of each product. You can use the COALESCE function here. If list_price is empty, use the lowest price min_price in calculation. If min_price is also empty, calculate the result 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