Re:

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

David FOXWELL wrote:
> Hi all,
>
> 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.
>   

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)       


Joe