为什么会收到“ [SQL0802]数据映射错误的数据转换错误”？例外？ [英] Why am I getting a "[SQL0802] Data conversion of data mapping error" exception?

问题描述

我对iseries / DB2不太熟悉。但是，我在一个将其用作主数据库的网站上工作。

I am not very familiar with iseries/DB2. However, I work on a website that uses it as its primary database.

最近在现有表中添加了新列。通过AS400查看它时，我看到以下数据类型：

A new column was recently added to an existing table. When I view it via AS400, I see the following data type:

Type: S
Length: 9
Dec: 2

这告诉我这是一个数字字段，小数点前有6位数字，并且小数点后两位。

This tells me it's a numeric field with 6 digits before the decimal point, and 2 digits after the decimal point.

当我使用简单的SELECT查询数据时（从MYTABLE中选择MYCOL ） ，我没有问题地取回了所有记录。但是，当我尝试使用 DISTINCT ， GROUP BY 或 ORDER BY 在同一列上，我得到以下异常：

When I query the data with a simple SELECT (SELECT MYCOL FROM MYTABLE), I get back all the records without a problem. However, when I try using a DISTINCT, GROUP BY, or ORDER BY on that same column I get the following exception:

[SQL0802] Data conversion of data mapping error

我推断出至少有一条记录具有无效数据-我的DBA称之为空白或 4 O 。怎么可能呢？尝试将无效数据添加到该列时，数据库不应该抛出异常吗？

I've deduced that at least one record has invalid data - what my DBA calls "blanks" or "4 O". How is this possible though? Shouldn't the database throw an exception when invalid data is attempted to be added to that column?

有什么办法可以解决此问题，例如过滤掉那些我的查询中有错误的记录？

Is there any way I can get around this, such as filtering out those bad records in my query?

推荐答案

我唯一能找到的解决方案是编写一个脚本来检查列中的空白值然后将它们更新为零。

The only solution I could find was to write a script that checks for blank values in the column and then updates them to zero when they are found.

这篇关于为什么会收到“ [SQL0802]数据映射错误的数据转换错误”？例外？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！