Re: Finding gaps in a sequence of invoice numbers using SQL

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

Jonathan Mason wrote:
> Hi Loyd
>
> That's exactly right.  We're allowed to use Query to interrogate the data
> for investigative purposes on the production server, but they have to be
> submitted to batch (F5 and interactive query is disabled).
>
> QMQRY and any SQL that does maintenance (very little) has to go through
> Change Management and be signed off before it can be run.
>
> All I'm trying to do here is to identify blocks of missing invoice numbers
> for an issue I'm working on.  What I'll end up doing is identifying the
> start of the range and then doing a similar thing to find the end of the
> range.
>   

Others have given you suggestions to find your gaps.  One the list we 
actually went through this exercise a while back and came up with some 
creative SQL that not only identified the gaps, but the beginning and 
ends of them.  A cursory search didn't find them, but I know they're out 
there.

However, I would like to offer a suggestion regarding the "no RPG" 
rule.  Can you possibly suggest to your auditors that RPG programs be 
allowed provide they run under a user profile that has no update rights 
to the file?  Since you're using query it seems that read access is 
allowed.  Therefore, an RPG program might also be allowed if it were 
limited to input operations only, which would be easy enough to do 
(attempting to run an RPG program that updated data with that same 
profile would result in an error).

You should probably set up a user profile that way anyway.  It's a good 
way to ensure that those "inquiry" jobs really are inquiries...

Just a thought.

Joe