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

Hi Chuck

Sorry, I should have re-read my post to see if it made sense when I wrote it
- I was in a rush to go home. Ideally identifying everything would be good,
but finding out where the gaps start is good enough for my purposes at the
moment.

Thanks

Jonathan

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: 07 August 2008 18:10
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Finding gaps in a sequence of invoice numbers using SQL

Actually the result would be 6, which is exactly what was originally
described; i.e. when ignoring both the subject line & the lead-in text.
Specifically, the clarification of /gap/ was later described instead
as a "list of invoice numbers where the _next invoice number_ doesn't
exist."

To instead get a list of all invoice numbers that do not exist, then
something like the following should work:

<code>
with
max_invno (max_invno) as
( select max(invno)
from INVHDR
) /* optional; guarantees run once before recursive query */
,seq_nbrs (seq_nbr) as
( select decimal(1, 6, 0) from sysibm.sysdummy1
union all
select decimal(seq_nbr+1, 6, 0) from seq_nbrs
where seq_nbr < (select max_invno from maxinvno)
)
select invno
from INVHDR
exception join seq_nbrs
on invno = seq_nbr
</code>

Regards, Chuck

Wilt, Charles wrote:

Note that this probably will _NOT_ give you what you are looking for.

If # 7,8,9 are missing, the only one you'll get returned is 7.

Jonathan Mason wrote:

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.

Jonathan Mason wrote:

<<SNIP>>
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). <<SNIP>>