The Oracle COALESCE
function is a handy function, but it can be hard to make it work if you want to consider fields with different data types. I'll show you how you can do this in this tip.
What Is Oracle COALESCE?
The Oracle COALESCE function allows you to check multiple values and return the first non-NULL
value.
The syntax of this function is:
COALESCE( expr1, expr2, [expr...] )
It checks each of the expressions in order, and returns the first one it finds that is not NULL
.
However, all of the expressions need to be the same data type.
Using Different Data Types
What if you wanted to use the COALESCE
function on columns or values that had different data types?
Let's see an example of this.
Say you had a table that looked like this:
CREATE TABLE customers (
first_name varchar2(100),
last_name varchar2(100),
country varchar2(20),
full_address CLOB,
employees number,
start_date date
);
If we ran a SELECT
on this table (which I had populated with some data), it may look like this:
FIRST_NAME | LAST_NAME | COUNTRY | FULL_ADDRESS | EMPLOYEES | START_DATE |
---|
Adam | Jones | USA | 10 Main Street | 20 | 12/JAN/15 |
Brad | Smith | USA | (null ) | 45 | 04/SEP/15 |
Carrie | Johnson | USA | 14 Long Avenue | 6 | (null ) |
(null ) | Lane | USA | 1 Main Road | 2 | (null ) |
What if I wanted to use a COALESCE
function and replace the first_name
with the full_address
?
SELECT first_name, full_address, employees, start_date,
COALESCE(first_name, full_address) AS coal
FROM customers;
This will give me an error, because the two fields have different data types.
ORA-00932: inconsistent datatypes: expected CHAR got CLOB
How can I fix this?
How to Use Coalesce With Different Data Types
The way to use the COALESCE
function with different data types is to convert all parameters to be the same data type as the first parameter.
If we did want to use a CLOB
value to substitute for a NULL VARCHAR2
value, then we could use the TO_CHAR
function on the CLOB
value.
SELECT first_name, full_address, employees, start_date,
COALESCE(first_name, TO_CHAR(full_address)) AS coal
FROM customers;
FIRST_NAME | FULL_ADDRESS | EMPLOYEES | START_DATE | COAL |
---|
Adam | 10 Main Street | 20 | 12/JAN/15 | Adam |
Brad | (null ) | 45 | 04/SEP/15 | Brad |
Carrie | 14 Long Avenue | 6 | (null ) | Carrie |
(null ) | 1 Main Road | 2 | (null ) | (null ) |
This has worked. It's because the COALESCE
function needs to use the data type of the first parameter.
What if you wanted to use the employees field, or any other numeric field?
You could convert that as well.
SELECT first_name, full_address, employees, start_date,
COALESCE(first_name, TO_CHAR(employees)) AS coal
FROM customers;
What if you wanted to use a number as the first parameter, and the other parameters are VARCHAR2
values?
You could try to convert the first parameter to match, because you can't convert VARCHAR2
to NUMBER
.
SELECT first_name, full_address, employees, start_date,
COALESCE(TO_CHAR(employees), first_name) AS coal
FROM customers;
What if you were using a date field?
SELECT first_name, full_address, employees, start_date,
COALESCE(start_date, first_name) AS coal
FROM customers;
You'll get a similar error when running this query.
ORA-00932: inconsistent datatypes: expected DATE got CHAR
How can we resolve this?
Let's try converting the start_date
to a string
using TO_CHAR
.
SELECT first_name, full_address, employees, start_date,
COALESCE(TO_CHAR(start_date), first_name) AS coal
FROM customers;
FIRST_NAME | FULL_ADDRESS | EMPLOYEES | START_DATE | COAL |
---|
Adam | 10 Main Street | 20 | 12/JAN/15 | 12/JAN/15 |
Brad | (null ) | 45 | 04/SEP/15 | 04/SEP/15 |
Carrie | 14 Long Avenue | 6 | (null ) | Carrie |
(null ) | 1 Main Road | 2 | (null ) | (null ) |
Yes, this seems to work.
So, converting data types of the parameters to match the first parameter seems to work for many different data types. This is something to remember if you're trying to use COALESCE
with different data types.