× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Alan,

We aren’t making any changes to the raw data in the uploaded file. We are just selecting certain fields from it and trying to insert them into another file that is cleared at the start of the process.

Thanks for the suggestion.


Dean Eshleman Software Development Architect

Everence®
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/>



From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan Campin
Sent: Thursday, March 28, 2024 10:55 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] Re: SQL error handling when converting character to numeric

This email originated outside of Everence. Do not click links or attachments unless you recognize the sender.

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<mailto: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<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related
questions.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.