Purpose
COALESCE() returns the first non-null expression in the parameter list that contains at least two parameters.
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).
Note
The values in the parameter list must be of consistent data types, except for non-null expressions.
Return type
The return type is the same as the data type of the first non-null
expr.If all parameters are
NULL,NULLis returned.
Examples
Create the tbl1 table, where 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 discounted price to 80% the original price, 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