Oracle Database Discussions

Sure.

TUBBY_TUBBZ? With
  2     customer_data as
  3  (
  4     select 2  as cust_id from dual union all
  5     select 3  as cust_id from dual union all
  6     select 6  as cust_id from dual union all
  7     select 7  as cust_id from dual union all
  8     select 11 as cust_id from dual union all
  9     select 12 as cust_id from dual
 10  ),
 11     all_ids as
 12  (
 13     select
 14        min_cust_id + level - 1 as generated_cust_id
 15     from
 16     (
 17        select
 18           1            as min_cust_id,
 19           max(cust_id) as max_cust_id
 20        from
 21           customer_data
 22     )
 23     connect by level <= max_cust_id - min_cust_id
 24  )
 25  select
 26     a.generated_cust_id
 27  from
 28     all_ids        a,
 29     customer_data  c
 30  where a.generated_cust_id = c.cust_id (+)
 31  and   c.cust_id is null
 32  order by a.generated_cust_id asc;

 GENERATED_CUST_ID
------------------
                 1
                 4
                 5
                 8
                 9
                10

6 rows selected.

Elapsed: 00:00:00.03

outstanding! Thanks! I am amazed how fast you found the solution
We are looking for a very good database developer to join our group and our project lead offered this puzzle to put on the interview test. Forgive me please if my question is not appropriate. Do you know of any US citizen with your skill level who would be interested to apply for database programmer position? We have wasted so much time already with staffing agencies candidates...

Or simpler:

with customer_data as (
                       select 2  as cust_id from dual union all
                       select 3  as cust_id from dual union all
                       select 6  as cust_id from dual union all
                       select 7  as cust_id from dual union all
                       select 11 as cust_id from dual union all
                       select 12 as cust_id from dual
                      )
select  column_value missing_cust_id
  from  (
         select  lag(cust_id,1,0) over(order by cust_id) + 1 gap_start,
                 cust_id - lag(cust_id,1,0) over(order by cust_id) - 1 gap_count
           from  customer_data
        ) t,
        table(
              cast(
                   multiset(
                            select  gap_start + level - 1
                              from  dual
                              connect by level <= gap_count
                           )
                   as sys.OdciNumberList
                  )
             )
  where gap_count > 0
  order by gap_start
/

MISSING_CUST_ID
---------------
              1
              4
              5
              8
              9
             10

6 rows selected.

SQL> 

SY.

Thanks :)

I'm not aware of any US citizens, but that's likely because i'm Canadian and haven't ventured across the border for work yet.

You should be able to find a suitable candidate via a staffing firm, but you'll likely have to be very specific with what you are looking for. Years of experience, desire for continual learning (some developers have been coding for 20 years, but never bother to update their skills so they are still stuck in cursor loops and the like), things like that.

Good luck in the search!

Actually, unless max(cust_id) is very large number and there are few gaps:

with customer_data as (
                       select 2  as cust_id from dual union all
                       select 3  as cust_id from dual union all
                       select 6  as cust_id from dual union all
                       select 7  as cust_id from dual union all
                       select 11 as cust_id from dual union all
                       select 12 as cust_id from dual
                      )
 select  level missing_cust_id
   from  dual
   connect by level < (select max(cust_id) from customer_data)
minus
 select  cust_id
   from  customer_data
order by missing_cust_id
/

MISSING_CUST_ID
---------------
              1
              4
              5
              8
              9
             10

6 rows selected.

SQL> 

SY.

Yup, which is mostly what i had in my original post, though i took the anti-join approach over the minus.

Though i started using MIN and MAX since i didn't see the OP wanted to include values outside the range contained within the table values. I got lazy so didn't change to that exact variant .... since it's not production code i didn't think anyone would mind :)

I liked the other solution you posted though, cool stuff.

I like recursive with clause B-)

with t(Val) as(
select *
  from table(sys.odciNumberList(2,3,6,7,11,12))),
rec(LagVal,Val) as(
select LagVal,Val-1
from (select Lag(Val,1,0) over(order by Val) as LagVal,Val
        from t)
where LagVal < Val-1
union all
select LagVal,Val-1
  from rec
where LagVal < Val-1)
select*from rec order by Val;

LagVal  Val
------  ---
     0    1
     3    4
     3    5
     7    8
     7    9
     7   10

Looks like this was the least costly solution, in terms of performance; according to explain plan
However, all of them are very interesting, thanks!