SQL0802 - Data Conversion or data mapping error

[v***@cityofcocoabeach.com]

Hi, I'm new to AS/400 and DB2 (we're currently at ver 4.5)

I've got a C# application that uses an ODBC connection to retrieve data from a DB2 database. When running the application I get the following error: [OdbcException: ERROR [HY000] [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0802 - Data conversion or data mapping error.]

I was able to identify the record that's causing the exception. It looks like the following sql statement

SELECT * FROM CXLIB.TABLE WHERE CAST(SUBSTR(LTRIM(CXLIB.TABLE.FIELD), 2, LENGTH(CXLIB.TABLE.FIELD)) AS INTEGER) > 40

which is trying to filter by dollar amounts is failing when encountering the first dollar amount that contains a comma. I've tried to determine the source data type, but when I run a File Field Description Listing from WRKDBF on the AS/400, the field type for this field is simply described as 'A', which I'm told stands for Alphanumeric(?) but I can't seem to find any reference to such a type in the DB2 SQL reference I obtained online. I've tried removing the comma with a REPLACE function, but apparently our version doesn't support this built-in function.

Any suggestions on how to work around this problem?

Thanks in advance...

[Kent Milligan]

You can find the SQL view of the column definition by querying the
SYSCOLUMNS view in QSYS2 schema.

'A' maps to a CHAR data type in SQL - to see it sounds like you're
trying to convert a character string value that contains non-numeric
characters.

Post by v***@cityofcocoabeach.com
Hi, I'm new to AS/400 and DB2 (we're currently at ver 4.5)
I've got a C# application that uses an ODBC connection to retrieve data from a DB2 database. When running the application I get the following error: [OdbcException: ERROR [HY000] [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0802 - Data conversion or data mapping error.]
I was able to identify the record that's causing the exception. It looks like the following sql statement
SELECT * FROM CXLIB.TABLE WHERE CAST(SUBSTR(LTRIM(CXLIB.TABLE.FIELD), 2, LENGTH(CXLIB.TABLE.FIELD)) AS INTEGER) > 40
which is trying to filter by dollar amounts is failing when encountering the first dollar amount that contains a comma. I've tried to determine the source data type, but when I run a File Field Description Listing from WRKDBF on the AS/400, the field type for this field is simply described as 'A', which I'm told stands for Alphanumeric(?) but I can't seem to find any reference to such a type in the DB2 SQL reference I obtained online. I've tried removing the comma with a REPLACE function, but apparently our version doesn't support this built-in function.
Any suggestions on how to work around this problem?
Thanks in advance...

[v***@cityofcocoabeach.com]

Hello Kent,

I'm totally 'green' with regard to DB2 and the AS/400. How do I query the SYSCOLUMNS view? I found the Query Utilities option from the files menu off the AS/400 main menu, but I don't see any reference to schemas. Am I off track here?

Can you suggest any way to manipulate the string so as to remove the non-numeric character within my .NET application's dynamic sql query?

Or can you direct me to documentation that would help a AS/400 newbie like me?

Thanks...

[Kent Milligan]

The iSeries SQL Reference can be found at: ibm.com/iseries/db2/books.html

Hard to give an exact solution without knowing which non-numeric
characters are causing problems.

Post by v***@cityofcocoabeach.com
Hello Kent,
I'm totally 'green' with regard to DB2 and the AS/400. How do I query the SYSCOLUMNS view? I found the Query Utilities option from the files menu off the AS/400 main menu, but I don't see any reference to schemas. Am I off track here?
Can you suggest any way to manipulate the string so as to remove the non-numeric character within my .NET application's dynamic sql query?
Or can you direct me to documentation that would help a AS/400 newbie like me?
Thanks...

[v***@cityofcocoabeach.com]

I located an SQL reference that pertains to our version.

Thanks for responding.

[Dieter Bender]

Hi,

SELECT *
FROM CXLIB.TABLE
WHERE CAST(
SUBSTR(
translate(LTRIM(CXLIB.TABLE.FIELD), '.', ',')
, 2, LENGTH(CXLIB.TABLE.FIELD))
AS INTEGER) > 40

might help (if the problem is caused by comma only)

Dieter Bender

Post by v***@cityofcocoabeach.com
Hi, I'm new to AS/400 and DB2 (we're currently at ver 4.5)
I've got a C# application that uses an ODBC connection to retrieve data
from a DB2 database. When running the application I get the following
error: [OdbcException: ERROR [HY000] [IBM][Client Access Express ODBC
Driver (32-bit)][DB2/400 SQL]SQL0802 - Data conversion or data mapping
error.]
I was able to identify the record that's causing the exception. It looks
like the following sql statement
SELECT * FROM CXLIB.TABLE WHERE CAST(SUBSTR(LTRIM(CXLIB.TABLE.FIELD), 2,
LENGTH(CXLIB.TABLE.FIELD)) AS INTEGER) > 40
which is trying to filter by dollar amounts is failing when encountering
the first dollar amount that contains a comma. I've tried to determine
the source data type, but when I run a File Field Description Listing from
WRKDBF on the AS/400, the field type for this field is simply described as
'A', which I'm told stands for Alphanumeric(?) but I can't seem to find
any reference to such a type in the DB2 SQL reference I obtained online.
I've tried removing the comma with a REPLACE function, but apparently our
version doesn't support this built-in function.
Any suggestions on how to work around this problem?
Thanks in advance...

[v***@cityofcocoabeach.com]

Hi Dieter,
Translating the comma to a period still gave me a data conversion error (I'm guessing because it results in more than one period for the given data?).
However, I was able to use a Case expression based on the field's length to build the string without the comma, like this:

case when length(ltrim(xclib.table.field)) < 8 then
SUBSTR(LTRIM(FIELD), 2, LENGTH(FIELD))
when length(ltrim(field)) = 9 then
substr(ltrim(field), 2, 1) || Substr(ltrim(field), 4, 6)
when length(ltrim(field)) = 10 then
substr(ltrim(field), 2, 2) || Substr(ltrim(field), 5, 6)
etc...

Thanks for your input.