Re: detecting "bad data" using an sql statement

Buck, thank you - that is excellent information.

Here's where we are on this...

SQL as you know is not wanting to move this data to a new table.

However, the data currently exists in our PF's and our applications which
are 99% rpg pgms do not blow up.

Why is this?

On Sat, Jan 13, 2018 at 7:19 AM, Buck Calabro <kc2hiz@xxxxxxxxx> wrote:

On 12 January 2018 at 09:01, Jay Vaughn <jeffersonvaughn@xxxxxxxxx> wrote:

So the only way i know to detect bad data using sql is when doing the
actual insert of the data into another file.

I have walked many, many miles in these shoes.
The only way to solve the issue permanently is to edit the data before
it goes into the file. That, and find the last remnants of program
described files, make them externally described and stop putting
blanks in packed fields :-) This is a lot of work for very little
perceived benefit, but if you can't trust the data, what's the point?

Now that you've found and rectified the programs that are corrupting
the data, you will need to go through the database and identify the
corrupted rows.

Is there "select" way of doing it instead?

Yes. ish.

select keycolumn, hex(prefix)
from ldataicl.srvfm50
where hex(prefix) = '4040404040'
order by keycolumn;

The trick here, if you will, is to keep the database manager from
trying to interpret PREFIX as a number.
The problem is that PREFIX might not be blanks. It might be something
else like 12.34, with the decimal point actually in the columns. For
those situations, I've 'scanned' for '4B'. The situation rapidly gets
uglier as more patterns are discovered.

In my opinion, the best generic way to find these devils in all
columns of a file is to write a program that writes a program. Query
all the numeric columns out of SYSCOLUMNS where table = 'SRVFM50'.
Use RPG record level access (Input Primary) to read the file. For
each of the numeric columns, call your sub-procedure that tests for
numericalness. SYSCOLUMNS will tell you which ones are numeric.

That sounds like a lot more work than you were hoping to do, and I'm
sorry to be the messenger.

also when a select is done in STRSQL
over the file, the bad data shows up as "+++++", so just need to know how
STRSQL detects it and shows "+++++" instead of the data.

As far as I know, we can't turn that off.
--buck
--
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