Coalesce in oracle

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.

comments powered by Disqus