Re: SQL error handling when converting character to numeric

[Sue Romano <slromano@xxxxxxxxxx>]

Check out the TRY_CAST function.  It lets you "try" to cast the value to your target data type.  If it fails, it returns null.  It has been available for IBM i 7.4 and later for about 2 years.
https://www.ibm.com/support/pages/node/6575533



> We receive a file from a third party that we upload to our system and then process.  One of the fields in the uploaded file is defined as character and it contains values like this >'9999999X1'.  We process this data with a single SQL statement to select data and then insert it into another file.  We expect those first 7 characters to be numeric and try to >convert them to an integer in the SQL statement.  However, sometimes the data wasn't setup correctly on the source system and those first 7 characters are not numeric.  >Therefore, the entire batch of data is not processed.  I would like the rest of the valid data to be processed like normal and skip the bad record.  We do attempt to weed out >some of the bad data by selecting where field1 > '1000000 '.  The case that tripped us up this last time was where the field contained '999999X1'.  The number was one digit >short.  Another thing to note is that this SQL statement is being executed in a CL program using an SQL command that we cre  ated long before IBM came out with their >RUNSQL command.

>

> What I'm trying to find is a way to identify the data that is bad before trying to process it.  Is there an SQL function that could test a field if it is numeric?  In this case, I would >use a substring to test the first 7 characters.  Either that or is there an "on error" clause at the field level?  I suppose I could write my own user defined function to do it, but it >seems like this is something people would deal with quite often.

>

> I do know that if I write an RPG program and process each record one at a time the conversion error could be handled appropriately.  I'm just trying to see if it can be done >using a couple SQL statements that process the entire file instead.


Sue Romano
Db2 for IBM i development