Db2 Coalesce Function Is Returning Nulls

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:

Solution 1:

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

SELECT ONORNO, ONIVRF, 
    CASEWHEN UCASE(SUBSTR(ONIVRF,1,5)) = LCASE(SUBSTR(ONIVRF,1,5)) THENCAST(SUBSTR(ONIVRF,1,5) ASNUMERIC)
        ELSE99999ENDAS 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,
    CASEWHENTRANSLATE(ONIVRF, '~~~~~~~~~~~', '0123456789-') ='~~~~~~~~'THENCAST(SUBSTR(ONIVRF,1,5) ASNUMERIC)
        ELSE99999ENDAS fred
FROM OINVOL