DB2 Coalesce function is returning nulls

Question

I am using DB2 for IBM i V6R1, and I am trying to convert a string value which sometimes has a valid representation of a number in it into a number. What I came up with was this:

select onorno, onivrf, coalesce(cast(substr(onivrf,1,5) as numeric),99999) as fred
from oinvol

sometimes the ONIVRF field has data like '00111-11', sometimes it has data like 'FREIGHT'.

The documentation leads me to believe that for data like this:

ONORNO ONIVRF
12     11010-11
13     FREIGHT
14     00125-22

I should get output like this:

ONORNO ONIVRF    FRED
12     11010-11  11010
13     FREIGHT   99999
14     00125-22  125

instead, I am getting this:

ONORNO ONIVRF    FRED
12     11010-11  11010
13     FREIGHT   NULL
14     00125-22  125

(If I skip the coalesce() and just use the Cast(substr(onivrf(1,5) as numeric), I get exactly the same results.)

What am I doing wrong here?

I'm surprised you're not getting an error attempting to cast 'FREIG' to numeric. — Joe Stefanelli
I'm running on the same platform, but I'm not getting null, I'm getting the 'replacement character' (which is + - null is indicated with a -). There's also other behaviour related to this (IS NULL doesn't work, for example) that leads me to believe that I'm getting back what amounts to a NaN result for the operation... somehow. — Clockwork-Muse
Yeah, I probably should have mentioned that I tried spelling out "CASE when X is null" and "ifnull(x,etc)" and got the same behavior. It still shows up in my output as a null. Perhaps it's a 'feature' of my client software. — Hellion

bhamby bhamby · Accepted Answer · 2011-12-15T22:47:02

If you're just trying to get rid of ONIVRFs that are all alphabetic characters, you can do something like this:

SELECT ONORNO, ONIVRF, 
    CASE 
        WHEN UCASE(SUBSTR(ONIVRF,1,5)) = LCASE(SUBSTR(ONIVRF,1,5)) THEN CAST(SUBSTR(ONIVRF,1,5) AS NUMERIC)
        ELSE 99999
    END AS fred
FROM OINVOL

It's a little hackish, because DB2 doesn't have a ISNUMERIC() equivalent. But alphabetic characters are the only ones that will be translated by the up- and lower-case functions.

I tested this on DB2 for z/OS (v9), and it worked, but I'm not sure if DB2 for iSeries is exactly the same. On mine, it did as @Joe Stefanelli said, and raised an error when it tried to cast an alphabetic string to NUMERIC.

Edit:

This might work better (assuming that you won't have any ONIVRFs that are all tildes). It shouldn't have the problem that @X-Zero mentions where some characters in languages other than English don't have lower and upper-case.

SELECT ONORNO, ONIVRF,
    CASE
        WHEN TRANSLATE(ONIVRF, '~~~~~~~~~~~', '0123456789-') = '~~~~~~~~' THEN CAST(SUBSTR(ONIVRF,1,5) AS NUMERIC)
        ELSE 99999
    END AS fred
FROM OINVOL

DB2 Coalesce function is returning nulls

1 Answers