Missing numbers ( was Re: )

[Tom Liotta <qsrvbas@xxxxxxxxxxxx>]

Michael_Schutte@xxxxxxxxxxxx wrote:

> sorry I put the +1 on the wrong field.

For all of the discussion so far, and without actually running any 
of the examples, I haven't determined whether [+1] is sufficient. In 
the example, both '7' and '8' are sequentially missing. Would [+1] 
find '7' but miss '8'?

Maybe more interesting but also without doing any testing, I wonder 
if lookahead fields in RPG might be useful. They require 
program-described files, but might be relatively quick. A lookahead 
would not only tell if any are missing but how many were missing 
sequentially.

Tom Liotta

> midrange-l-bounces@xxxxxxxxxxxx wrote on 04/16/2008 08:18:25 AM:
>
> > Exec SQL
> > Select  Min(Field) FROM FILE  a
> >  WHERE not exists (select '1' from file b where a.key1 = b.key1 and
> a.field
> > = b.field+1)
> >
> > midrange-l-bounces@xxxxxxxxxxxx wrote on 04/16/2008 04:57:07 AM:
> >
> > > Is there a way to select the numbers missing from a list of numbers ?
> > >
> > > I mean, if I have a table with keys  1, 2, 3, 5, 6, 9,
> > >
> > > can I find the numbers missing between 1 and 10? ie, return 4, 7, 8,
> 10.
> > > I could create a table with 1 zone filled with the numbers 1 to 10
> > > and then do an outer join. But I need this in a program. I need to
> > > find the smallest missing number in a given range of numbers.
> > >
> > > Thanks.