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

["midrange" <franz9000@xxxxxxxxx>]

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.