Coalesce in oracle

COALESCE return first not null value out all the parameter passed into this function. NOTE: All the parameters have be same datatype

DROP TABLE coalesce_func_test;

CREATE TABLE coalesce_func_test
(
   id     NUMBER,
   col1   VARCHAR2 (10),
   col2   VARCHAR2 (10),
   col3   VARCHAR2 (10),
   col4   NUMBER
);

Sample Data

SET DEFINE OFF;

INSERT INTO coalesce_func_test (id,
                                col1,
                                col2,
                                col3,
                                col4)
     VALUES (1,
             10,
             20,
             30,
             40);

INSERT INTO coalesce_func_test (id,
                                col1,
                                col2,
                                col3)
     VALUES (2,
             31,
             32,
             33);

INSERT INTO coalesce_func_test (id,
                                col1,
                                col2,
                                col4)
     VALUES (3,
             41,
             42,
             43);

INSERT INTO coalesce_func_test (id,
                                col1,
                                col3,
                                col4)
     VALUES (4,
             51,
             53,
             54);

INSERT INTO coalesce_func_test (id,
                                col2,
                                col3,
                                col4)
     VALUES (5,
             72,
             73,
             74);

COMMIT;

ID	COL1	COL2	COL3	COL4
1	10	20	30	40
2	31	32	33
3	41	42		43
4	51		53	54
5		72	73	74

Try it

SELECT dt.*,
       COALESCE (col1,
                 col2,
                 col3,
                 col4) COALESCE
  FROM coalesce_func_test dt
ORDER BY 1;

All the Columns have to be same data type
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

SELECT dt.*,
       COALESCE (col1,
                 col2,
                 col3,
                 to_char(col4)) COALESCE
  FROM coalesce_func_test dt
ORDER BY 1;

ID	COL1	COL2	COL3	COL4	COALESCE
1	10	20	30	40	10
2	31	32	33		31
3	41	42		43	41
4	51		53	54	51
5		72	73	74	72

• If we have only 2 parameters, then COALESCE works like NVL.

Oracle Functions