Re: (SQL) problems during select (QRY2283)

Yivi,

No, I don't want to, but I have to earn the right to change this things over
> here.


Well, fixing corrupt data should be an easy sell, or I'd think they have
even bigger problems...

Again, time to show my newbieness. What's "FIXNMR"? Searched for it but
> found no results.


It was a typo, sorry.  It should have been FIXNBR(*INPUTPACKED).  When
compiling a RPGLE module, one of the compiler options is FIXNBR() with the
default being *NONE.  Look at the help text for the FIXNBR() keyword on the
CRTRPGMOD command, which says in part:

  "*INPUTPACKED

  Indicates that if packed decimal data that is not valid is encountered
while processing input specifications, the internal variable will be set to
zero."

Thus by using a one-time program to read all the input records and rewrite
them, all "blank" packed values will be reset to packed zeros.  However, as
I previoiusly mentioned, if you are sure the only field(s) in error are some
specific fiels such as ANOVTO, then you'll get much better performance by
using a program described file testing those bytes for blanks and only
updating records in error.

>  2) Attach a trigger to the file using *BEFORE and *ALWREPCHG then have it
>
> Sounds like a great idea. Whenever I am allowed to,


If you do the first step without the second, you still have a problem
waiting to happen when the rogue program(s) put invalid data in the file
again.

Program described files it is.


If they have old programs using program described files which are known to
put invalid data in files and they *won't* agree to let the file get fixed,
I'd be very worried about their priorities.

It would take under 5 minutes and 10 lines of code to write the one-time fix
program.  The trigger would take somewhat longer if you have no other
trigger programs to cannabolize as a template, but IMHO learning how to do
triggers is a very worthwhile endeavor.

> What happens if you create a view where ANOVTO is a character field?
> >
> I will give it a try today. Not feeling overly optimistic, but the idea
> deserves a chance! :)


I'd say this is the best alternative yet offered.  Unless you can get by
with a view which doesn't even include the field at all.

But IMHO the data still deserves to get fixed.  And if nobody knows which
program(s) may be in error and won't authorize someone to examine all
programs which update that file as a program described file, then a trigger
program is the best way to maintain integrity and get notified which
program(s) are corrupting the data.

Doug