ibm-midrange - 连字符在 Db2 for i 查询结果中表示什么？

Question

我想过滤掉以下查询中包含破折号的行。 PRMWT 列是 DECIMAL (9, 3)。

查询

SELECT
    PPA#,
    PRMWT
FROM FUTMODS.SCPMSTQ
WHERE PPA# = '20829161'

结果

PPA#        PRMWT
20829161    53.513
20829161    -
20829161    -
20829161    -
20829161    -
20829161    -

如果我添加条件 AND PRMWT IS NOT NULL，结果是一样的，我会收到此警告:

SQL State: 01565 Vendor Code: 802 Message: [SQL0802] Data conversionor data mapping error. Cause . . . . . :   Error type 6 has occurred.Error types and their meanings are: 1 -- Arithmetic overflow. 2 --Floating point overflow. 3 -- Floating point underflow. 4 -- Floatingpoint conversion error. 5 -- Not an exact result. 6 -- Numeric datathat is not valid. 7 -- Double-byte character set (DBCS) or UTF-8 datathat is not valid. 8 -- Division by zero. 9 -- Hash value cannot becomputed for the requested query. 10 -- User-defined function returneda mapping error. 11 -- Not valid length found in a varying-lengthcolumn returned from an array result set. 12 -- Result of aconcatenation operation on a varying-length field exceeded the maximumallowed length of the result type. If the error occurred whenassigning a value to a host variable of a FETCH, embedded SELECT, SET,or VALUES INTO statement, the host variable name is *N and therelative position of the host variable in the INTO clause is 2. If thehost variable name is *N, the error occurred when attempting toresolve a search condition. If more than one data mapping erroroccurred, this is a description of the first error that occurred.  Fora description of any other data mapping errors, see the previouslylisted messages in the job log. Recovery  . . . :   The error wascaused by data that was not valid or that was too large.  Look at thepreviously listed messages in the job log (DSPJOBLOG command) or pressF10 (Display messages in job log) on this display to determine whatrow and columns were involved in the error.  Correct the data and thentry the request again.

仔细检查后，我发现该列有 Nullable = No。好的，但是为什么它里面有一些非数字的东西(连字符)？

我还尝试了 PRMWT > 0、PRMWT > 0.0、PRMWT > '000000.000'、PRMWT != '-' , PRMWT != '', PRMWT = CAST(0.0 AS DECIMAL(9,3)), CAST(PRMWT AS FLOAT) > 0.0 等

我迷路了。在这个被遗忘的数据库引擎中，连字符到底意味着什么？我该如何操作它？操作系统版本为 7.2。

Answer 1

使用hex 函数查找PRMWT 字段中的十进制数据错误:

SELECT  PPA#, hex(PRMWT) hex_prmwt
FROM FUTMODS.SCPMSTQ
WHERE PPA# = '20829161'

要更正错误数据，您可以测试特定的十六进制值并使用 SQL UPDATE 将字段设置为 0。

update FUTMODS.SCPMSTQ
set    PRMWT = 0
where  hex(PRMWT) = '404040'

这是一篇好文章:https://www.itjungle.com/2013/08/07/fhg080713-story02/