× 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.



Wrapping a UDF around it is probably a good idea
Select ..., cleanThisColumn(CobolColumn)...
Instead of
Select ..., CobolColumn...
Would be a good idea. For some good examples look at Alan Campin's idate routines.



-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jay Vaughn
Sent: Wednesday, February 20, 2019 3:55 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL and invalid data in field

So occasionally we come across data in our files that SQL does not like to play with.
RLA handles it fine. But as we move more towards SQL it is becoming a problem.

Example

Embedded sql...

select * from table into :ds_table

when the fetch occurs to move the row into the host ds, we get the following error if "There is data in a decimal field that is not valid.

Message ID . . . . . . : CPF5035 Severity . . . . . . . : 10

Message type . . . . . : Diagnostic

Date sent . . . . . . : 02/20/19 Time sent . . . . . . :
15:26:00


Message . . . . : Data mapping error on member SRVCOLI.

Cause . . . . . : A data mapping error occurred on field

SRVCOLI_1.COLI_CHANGE_DATE in record number 0, record format *FIRST, member
number 1, in member SRVCOLI file SRVCOLI in library LDATASV3, because of

error code 1. The error code meanings follow:

1 -- There is data in a decimal field that is not valid.

2 -- A significant digit was truncated.

3 -- A floating point value exceeded the maximum representable value.

4 -- A floating point value was less than the minimum representable value.
5 -- A binary floating point value could not be used for the attempted

operation because it is not a number, or is not valid.

6 -- A floating point value could not be mapped to packed decimal, zoned

More...

I know the very first plan of action should be figure out where this invalid data is making its way into the file and my guess is a COBOL pgm, but at risk of major judgement, we just don't have time to go find and fix that. We have however looked into the issue and determined they are fields that are simply initialized incorrectly and then written out to the file.
Our suspicions are that a COBOL pgm does it.

Now, we have a simple fix pgm we can run over the file when we encounter this issue and it cleans the entire file. The fields essentially are initialized correctly back to 0. The pgm is short and sweet and looks like...

halwnull(*inputonly) fixnbr(*zoned)
fsrvcoli up e k disk
c update rcolst

That's all there is to it.

BUT I'm wanting to try and have SQL detect and handle the data during processing so that processing is not interrupted.

If anyone has any experience with this issue and wish to share the resolution I'd appreciate it.

Jay
--
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@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com

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.