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

[David FOXWELL <David.FOXWELL@xxxxxxxxx>]

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.