Re: [EXTERNAL] Re: detecting "bad data" using an sql statement

Jim, getting a chance to go through all the nice suggestions and I do like
this one, as i'm not sure there is a sure bet way in sql to detect the bad
data - best thing is to at least run this over the file and then see what
I'm left to deal with. GREAT offering... thanks!

On Sun, Jan 14, 2018 at 2:30 PM, midrange <franz9000@xxxxxxxxx> wrote:

Going to toss this in to the mix of answers - in quick skim of the thread,
did not find a clear definition of "what is bad data" to the original
requester (is it only decimal data issue, or also bad values in char
columns
(like carriage return line feed x'0D25' or many other possibilities?).
This 3 line rpgle code handles fixing blanks (x'0404') in numeric columns
in whole file, but not really fix other kinds of issues. We have an old
software pkg writing this junk and our external ms-sql pulling data chokes
on it, so we fix the file before scheduled pull - runs in seconds (and will
ignore the hoots for using an update primary definition). Will set all the
bad columns to zero. I think found example originally on this list..

HALWNULL(*INPUTONLY) FIXNBR(*ZONED)
Faddr up e k disk
C update @addr

Have other code for x'0D25' if needed

D pos s 3 0 inz
D pos1 s 3 0 inz
D @crlf s 2a inz(x'0D25')

eval pos = %scan(@crlf:@legal:pos1)
if pos>0

Jim Franz



-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon
Hamberg
Sent: Friday, January 12, 2018 4:05 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxx>
Subject: Re: [EXTERNAL] Re: detecting "bad data" using an sql statement

Hey Justin

There are all manner of ways for data to be put into a table and be corrupt
- I can think of some option on CPYF that would do it.

So it is not just native RPG I/o that can put data into a table, or SQL
INSERT or UPDATE - there are other mechanisms.

There IS a difference in the timing of when this is checked, a difference
between SQL and RPG.

Cheers
Vern

On 1/12/2018 1:22 PM, Justin Taylor wrote:

I don't know how that's possible. You mind sharing for posterity?


Copying the data into a clone of the original table should choke on the

invalid data. It might stop on each bogus row, which would be a pain if
there are a lot of them.

Since this is the RPG list, you could write an RPG program using I-specs

with your column(s) as character. Manually move the input field into a
numeric field and trap for the decimal data error.


--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD