COALESCE returns first not null value out of all the parameters passed into this function. NOTE: All the parameters have been the 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.
