Get started Bring yourself up to speed with our introductory content.

Excluding characters from AS/400 DB2 SELECT statements prevents mapping errors

In DB2 on AS/400, rows with characters that cannot be converted to a numeric value (A or #) need to be excluded from a SELECT statement in order to avoid a data mapping error when running the INT function on GLSBL.

We are running a unicode DB2 for JD Edwards. The CCSID is 13488. I am trying to create a view where we convert one of the alpha fields to a numeric field. We have nulls in some of the records,.so I am using SELECT IFNULL(INT (GLSBL),0) AS AB8, to convert the field. However, I am not getting 0 for the null values, but ++++++++ instead. The info message says to use VARCHAR to convert. Can this be done and will it convert my alpha values to numeric and null values to 0?
It's difficult to say without seeing the error messages and the entire SELECT statement, but the '+++++++' indicates that a data mapping error occurred when running the INT function on GLSBL. The most likely cause is that the GLSBL column contains characters that cannot be converted to a numeric value, such as 'A' or '#'. The rows with those values need to be excluded from your SELECT statement.

Dig Deeper on iSeries SQL commands and statements

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.