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

I have done this with an SQL sequence.

Create a temporary file from the sequence and run your file against this temporary file.

-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Jonathan Mason
Envoyé : jeudi 7 août 2008 19:32
À : 'Midrange Systems Technical Discussion'
Objet : 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>>

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.