Re:

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