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_DECODEcolumn 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."