65.9K
CodeProject is changing. Read more.
Home

How to Use COALESCE with Different Data Types

starIconstarIconemptyStarIconemptyStarIconemptyStarIcon

2.00/5 (6 votes)

Nov 7, 2016

CPOL

2 min read

viewsIcon

46654

Use the COALESCE function with different data types correctly

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_NAMELAST_NAMECOUNTRYFULL_ADDRESSEMPLOYEESSTART_DATE
AdamJonesUSA10 Main Street2012/JAN/15
BradSmithUSA(null)4504/SEP/15
CarrieJohnsonUSA14 Long Avenue6(null)
(null)LaneUSA1 Main Road2(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_NAMEFULL_ADDRESSEMPLOYEESSTART_DATECOAL
Adam10 Main Street2012/JAN/15Adam
Brad(null)4504/SEP/15Brad
Carrie14 Long Avenue6(null)Carrie
(null)1 Main Road2(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_NAMEFULL_ADDRESSEMPLOYEESSTART_DATECOAL
Adam10 Main Street2012/JAN/1512/JAN/15
Brad(null)4504/SEP/1504/SEP/15
Carrie14 Long Avenue6(null)Carrie
(null)1 Main Road2(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.