Re: SQL error handling when converting character to numeric

[Alan Campin <alan0307d@xxxxxxxxx>]

I would, personally, write an RPG program and quit messing with it. With
RPG you can generate an audit report. Also, what does the raw data look
like (Before update). Is the update process changing it?

On Thu, Mar 28, 2024 at 7:47 AM Dean Eshleman via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

> 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.
>
>
> Dean Eshleman Software Development Architect
>
> Everence(r)
> 1110 North Main Street
> PO Box 483
> Goshen, IN 46527
> P: (574) 533-9515
> TF: (800) 348-7468
> W: everence.com<http://www.everence.com/>
>
> Confidentiality Notice: This information is intended only for the
> individual or entity named. If you are not the intended recipient, do not
> use or disclose this information.  If you received this e-mail in error,
> please delete or otherwise destroy it and contact us at (800) 348-7468 so
> we can take steps to avoid such transmission errors in the future. Thank
> you.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.