I'd try to convert the first 7 digits into integer, but NOT with INT or CAST
but with TRY_CAST.
If something is wrong a NULL value is returned and a NULL value can be
trapped or at least converted with the COALESCE scalar function
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Dean
Eshleman via MIDRANGE-L
Sent: Thursday, 28 March 2024 15:47
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Dean Eshleman <Dean.Eshleman@xxxxxxxxxxxx>
Subject: 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.
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.
midrange.com
