Why is Oracle's DECODE giving me a different value than NVL?

3.7k views Asked by At

This query:

select nvl(0.75,0) from dual

gives me 0.75 (numeric) but this query:

select decode(1,0,null,0.75) from dual 

gives me '.75' (string).

Why?

I tried to fix this by changing the second query to:

select decode(1,0,null,to_char(0.75,'0.99')) from dual

but in my actual code the 0.75 will be a field (NUMBER) that may have a different number of decimal places and I'm not suppose to add/remove anything from that value.

Any ideas on how to fix the missing zero issue but still support all possible decimal lengths?

4

There are 4 answers

0
Ben On BEST ANSWER

It's because the 3rd parameter of your decode statement is NULL; as per the documentation1 (my emphasis).

Oracle automatically converts expr and each search value to the data type of the first search value before comparing.... If the first result has the data type CHAR or if the first result is null, then Oracle converts the return value to the data type VARCHAR2.

In your case the first result is NULL, which Oracle treats as a VARCHAR2. Your return value is being implicitly converted to a VARCHAR2. If you changed your DECODE() to the following you'd get a number:

select decode(1, 0, 0, 0.75)

and you could achieve your NULL by using the NULLIF() function:

select nullif(decode(1, 0, 0, 0.75), 0) ...

It's better to use a CASE statement, which enforces that all returned datatypes are the same:

select case 1 when 0 then null
              else 0.75
       end ...

1. which I've been caught out on as well.

0
GriffeyDog On

In the first case, nvl() is returning a numeric value. How to display that is up to the program you are using to run your queries. TOAD displays it like you said, 0.75.

In the second example, decode() is returning a varchar2. When Oracle converts a number to a string without any formatting, this is what you get, i.e. ".75".

From the Oracle docs on decode():

If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2.

You could use a number format and rtrim() to achieve your purpose, e.g.:

select rtrim(to_char(.75, '9990.99999999999999'),'0') from dual;

Result:

0.75
2
Girish Zope On

You can use

select decode(1,0,to_number(null),0.75) from dual
0
Piyushkumar Kachhadiya On
select to_number(decode(1,0,null,0.75)) from dual