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

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