Re: SQL error handling when converting character to numeric

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.