Re: (SQL) problems during select (QRY2283)

["Douglas Handy" <dhandy@xxxxxxxxx>]

Yivi,

Theoretically, ANOVTO is a packed(4,0). But, one or two of these old
> programs that no one really knows who wrote are inserting blanks where 0's
> (or any other number) should go,


I can't touch the file. Can't touch the code that's producing these results,
> at least not right now.


Why can't you correct the file contents?  You have an externally described
file with corrupt data, and you want to leave it that way?

Here is what I'd do:

 1) Write a RPGLE program using FIXNMR(*INPUTPACKED) to process the file one
time, reading each record and "fixing" the blanks to numerics.  Or if you
are sure it is only a couple of specific fields which are blanks instead of
packed zeros, performance would likely be better by using a
program-described input file testing the appropriate bytes for blanks then
updating the record with packed zeros in the fields.  This will correct
existing data.

 2) Attach a trigger to the file using *BEFORE and *ALWREPCHG then have it
examine the buffer and correct any blanks to packed zeros before allowing
the record to be written to disk.  This will keep the corruption from
happening in the future, without the need to touch the existing programs
which have the file defined wrong.  You could even have the trigger send you
a message with the program name or otherwise log when it performs one of
these updates, so that you over time learn what would have corrupted the
file.

However, for this to continue to occur, it implies the rogue program(s) are
either using program-described files or file level checking has been
disabled.  IMHO, that's not a desirable situation for a production
environment.

Doug