Функция DB2 Coalesce возвращает значения null

Я использую DB2 для IBM i V6R1, и я пытаюсь преобразовать строковое значение, которое иногда имеет действительное представление числа в нем в число. Я придумал следующее:

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

иногда поле ONIVRF имеет такие данные, как «00111-11», иногда оно имеет такие данные, как «FREIGHT».

Документация заставляет меня думать, что для таких данных:

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

Я должен получить вывод следующим образом:

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

вместо этого я получаю следующее:

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

(Если я пропущу coalesce() и просто использую Cast(substr(onivrf(1,5) as numeric) , я получаю точно такие же результаты.)

Что я здесь делаю неправильно?

Если вы просто пытаетесь избавиться от ONIVRF которые являются всеми алфавитными символами, вы можете сделать что-то вроде этого:

 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 

Это немного хаки, потому что DB2 не имеет эквивалент ISNUMERIC() . Но алфавитные символы являются единственными, которые будут переведены функциями верхнего и нижнего регистра.

Я тестировал это в DB2 for z / OS (v9), и он работал, но я не уверен, что DB2 for iSeries точно такая же. По моему, это произошло, как сказал @Joe Stefanelli, и поднял ошибку, когда попытался наложить алфавитную строку на NUMERIC .

Редактировать:

Это может работать лучше (при условии, что у вас не будет никаких ONIVRF , которые все тильды). У него не должно быть проблем, что @ X-Zero упоминает, что некоторые символы на других языках, кроме английского, не имеют нижнего и верхнего регистра.

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