Thursday, December 27, 2012

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

2 comments:

  1. You can also use to_number(null) inside the decode. I think that is more clear.
    decode(job, 'PRESIDENT', to_number(null), 0))
    By default null's data type is varchar2,

    ReplyDelete
  2. I agree - I've tried to make it a habit to be as explicit as possible with data types in CASE, DECODE, NVL... any of those mega overloaded functions.

    ReplyDelete