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?
It's because the 3rd parameter of your decode statement is NULL; as per the documentation1 (my emphasis).
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:and you could achieve your NULL by using the
NULLIF()
function:It's better to use a CASE statement, which enforces that all returned datatypes are the same:
1. which I've been caught out on as well.