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

[Jonathan Mason <jonathan.mason@xxxxxxxxxxxxxxxx>]

Not to worry, I managed it with the help of the News/400 Desktop Guide 
to SQL and came up with:

SELECT A.INVNO            
 FROM INVHDR AS A         
 EXCEPTION JOIN INVHDR AS B
 ON A.INVNO = B.INVNO + 1

I'm so impressed, now it's time to go home.

All the best

Jonathan

Jonathan Mason wrote:
> Hi Everyone
>
> One of our customers has an Invoice Header file that currently has 
> 815,000 records and is growing daily and I need to try and identify any 
> blocks of missing numbers.  I could do this quite easily with a simple 
> RPG (or even CL!) program, but would have to go through the full change 
> management rules to get it approved, tested, system tested, user tested, 
> deployed, etc, and there isn't the time.
>
> Is it possible using SQL to generate a list of invoice numbers where the 
> next invoice number doesn't exist.  For example, if invoice numbers 1, 
> 2, 3, 6, 7, 11, 12 and 15 existed the list would show 3, 7, 12 and 15 
> (although I'm not too worried about the last one).
>
> I'm doubting there is, but figured I would ask.
>
> Thanks
>
> Jonathan
>
>