RE: COALESCE not working as I expected

The reason it is returning a null instead of doing the coalesce is that the function errors out.

If you try this using VALUES instead of a SELECT such as this:
values to_number(0045E1020040), coalesce(to_number(0045E1020040), 0);
It will error out with
SQL State: 42820 Vendor Code: -410 Message: [SQL0410] Floating point literal 0045E1020040 not valid. Cause . . . . . :   The number of characters in the floating point constant 0045E1020040 cannot exceed 42 excluding leading zeros.  The number of digits in the first number cannot exceed 34 excluding leading zeros, and the number of digits in the second number cannot exceed 4. Recovery  . . . :   Correct the indicated literal 0045E1020040.  Make certain that the floating point literal is in the correct form shown by the following examples: +1.2E+3, 15E1, 2.E5, 2.2e-1, +5.E+2, -.1e1.  Try the request again.
It will not move on to the coalesce.

However, when you use a select it will use nulls and move on to the next row. So, when it hits
coalesce(to_number(0045E1020040), 0)
It does not break this down into two distinct operations. It does not break this down into
to_number(0045E1020040), 0)
then coalesce(null, 0)
It just returns the error and then converts it to a null.
So instead of coalesce(null, 0) you are seeing the results of coalesce(error, 0). Coalesce does not know how to handle an error so it returns a null.

And, no, you cannot nest these to fix it. IOW you cannot resolve it by
select to_number(mychar), coalesce(to_number(mychar), 0), coalesce(coalesce(to_number(mychar), 0), 0)
from qtemp.rr;
as you will just end up with 3 nulls.

Any possibility that you can start to embrace null values instead of fighting to replace them with some other default value, such as zero?

Rob Berendt