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



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
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.