Re:

[Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>]

<tic>

C'mon Joe, you know that's not allowed.  Everything must be done in SQL 
or it doesn't exist.  Solving the business issue has nothing to do with it.

</tic>

Joe Pluta wrote:
> Joe Pluta wrote:
>   
> > Hi David!
> >
> > The easiest way to do this is to look at every key in the file and find 
> > one where the next key doesn't exist.  To do that, create a temporary 
> > table with a list of next keys (potential holes).  If your lowest key 
> > may not be in the file, you have to include that manually in your list 
> > of potential keys.
> >
> > So just break it down by task:
> >
> >  // Create list of potential holes as every key in the file +1
> >  With T1 as                                               
> >    // Include 1 by using the system dummy file
> >    (select 1 as potential from sysibm/sysdummy1 union all
> >     select keyfield+1 as potential from keys)
> >  // Now select the smallest one without a match          
> >  select min(potential) from t1 where not exists           
> >    (select 1 from keys where potential = keyfield)       
> >
> >   
> >     
> And not to be contrary, but by far the easiest way to do this would be 
> to read the file sequentially by key and compare the key to the previous 
> key plus one.  If it doesn't match, previous key plus one is your 
> answer.  Start previous key at zero.
>
> Joe
>