DB2 Coalesce函数返回null [英] DB2 Coalesce function is returning nulls

问题描述

我正在使用DB2 for IBM i V6R1，而我正在尝试将一个字符串值转换为有效数字的有效表示形式。我想出的是这样的：

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

有时ONIVRF字段有数据像' 00111-11'，有时它的数据就像FREIGHT。

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

相反，我得到这个：

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

（如果我跳过 coalesce（），只需使用 Cast（substr（onivrf（1,5））作为数字） ，我得到完全相同的结果。）

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

我在这里做错了什么？

推荐答案

如果你只是想摆脱所有字母字符的 ONIVRF ，你可以这样做：

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.

我在DB2 for z / OS（v9）上进行了测试，它有效，但我不知道DB2 for iSeries是否完全相同。对我来说，它是@Joe Stefanelli所说的，当它试图将一个字母串转换为 NUMERIC 时，引发了一个错误。

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.

编辑

这可能会更好（假设你不会有任何 ONIVRF s都是波浪号）。它不应该有@ X-Zero提到的问题，除了英文以外的其他语言的字符没有大小写。

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函数返回null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！