SQL中的数据转换或数据映射错误

This is my WinSQL query that I try and execute.

这是我尝试执行的WinSQL查询。

SELECT ezait6 AS "MO No",
       vhitno AS "Item No",
       vhrefd AS "Finish Date",
       vhmaqa AS "Manuf Qty",
       vhmaun AS "U/M"
FROM m3edbtest.mwohed,
     m3edbtest.cinacc
WHERE ezcono = 1
      AND vhcono = ezcono
      AND vhrefd >= '20170801'
      AND vhrefd <= '20170831'
      AND vhmfno = ezait6;

--Different Data Types

- 不同的数据类型

--vhmfno ==> Integer

--vhmfno ==>整数

--ezait6 ==> nchar(8)

--ezait6 ==> nchar（8）

After I run my query, I got below errors:

运行查询后，我得到以下错误：

-- Error : SQL0802 - Data conversion or data mapping error.

I suspect that it is beclose of different data type I have a nvarchar column in one of my table "ezait6". So how should I convert that values to INT type.

我怀疑它是由于不同的数据类型我在我的一个表“ezait6”中有一个nvarchar列。那么我应该如何将该值转换为INT类型。

1 个解决方案

#1

You can check first if the content of the varchar field is numeric and query only on those:

您可以先检查varchar字段的内容是否为数字，并仅查询这些内容：

SELECT ezait6 AS "MO No",
       vhitno AS "Item No",
       vhrefd AS "Finish Date",
       vhmaqa AS "Manuf Qty",
       vhmaun AS "U/M"
FROM m3edbtest.mwohed,
     m3edbtest.cinacc
WHERE IsNumeric(ezait6)=1
      AND ezcono = 1
      AND vhcono = ezcono
      AND vhrefd >= '20170801'
      AND vhrefd <= '20170831'
      AND vhmfno = CONVERT(INT,ezait6);