Re: Query to return PK sequence gap ??

On Oct 9, 1:33 pm, krisl..._at_gmail.com wrote:
> Hi All,
>
> I want to make a query that return PK sequence gap.
> How to do that ?
>
> here is my DDL :
> Create Table EMP_TEST(
> emp_id number primary key,
> emp_name varchar2(30)
> );
>
> insert into EMP_TEST values (1, 'john');
> insert into EMP_TEST values (3, 'didier');
> insert into EMP_TEST values (5, 'cole');
>
> The query will have two bind variables :
> :emp_id_from and :emp_id_to
>
> If I pass 1 to :emp_id_from and 7 to :emp_id_to , the query should
> return :
>
> PK _GAP
> --------------
> 2
> 4
> 6
> 7
>
> Thank you for your help,
> xtanto

select sqgen.val pk_gap
  from (select (hundreds.x-1)*100+tens.x val

          from (select level x from sys.dual connect by level < 101) tens

              ,(select level x from sys.dual connect by level < 101) hundreds

       ) sqgen
 where sqgen.val between :emp_id_from and :emp_id_to    and not exists(select 1 from emp_test where emp_id=sqgen.val)

This will work for all values between 1 and 10000. It should be obvious how to extend the sequence generator subquery to return more values if needed. :)

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Tue Oct 09 2007 - 07:28:12 CDT