Decoding Decode Data Types

I ran into a strange issue in APEX a while ago which was due to a decode statement not returning the data type that I expected. Here’s a sample of what the query looked like:

create or replace view mdsouza_temp as
select
  decode(job, 'PRESIDENT', null, 0) col_decode,
  case when job = 'PRESIDENT' then null else 0 end col_case
from emp;

At first glance you would expect both columns to be the same. If you query the view they look like they return the same values but they don’t (this is what caught me). Upon further investigation the COL_DECODE column actually returns a VARCHAR2 and not a number:

desc mdsouza_temp;

Name       Null Type        
---------- ---- -----------
COL_DECODE      VARCHAR2(1)

COL_CASE        NUMBER     

It turns out that if you have a NULL as the first result (not the last, default, value) the returned value/data type will be a VARCHAR2 and not the data type of the other values as shown in the following example:

create or replace view mdsouza_temp as

select
  decode(job, 'MANAGER', 1, 'PRESIDENT', null, 0) col_decode,
from emp;

desc mdsouza_temp;

Name       Null Type  

---------- ---- ------
COL_DECODE      NUMBER

If you do have to have NULL as the first result in the set you need to explicitly convert the result to the appropriate data type:

create or replace view mdsouza_temp as

select
  to_number(decode(job, 'PRESIDENT', null, 0)) col_decode
from emp;

desc mdsouza_temp;

Name       Null Type  

---------- ---- ------
COL_DECODE      NUMBER   

This is not a bug, and is covered in the Oracle documentation on DECODE "Oracle automatically converts the return value to the same data type as the first result."