Re: Query to return PK sequence gap ??

From: Mladen Gogala <mgogala_at_yahoo.com>
Date: 09 Oct 2007 13:09:17 GMT
Message-ID: <470b7d7d$0$1341$834e42db@reader.greatnowhere.com>

On Tue, 09 Oct 2007 02:33:42 -0700, krislioe 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
  

  1 with keys as (select emp_id,

  2                       lead(emp_id,1) over (order by emp_id ) as nxt
  3                from emp_test
  4                order by emp_id)

  5 select level as pk_gap from dual connect by level < 8   6 minus
  7* select emp_id from keys where nxt - emp_id >1 SQL> /     PK_GAP

---

         2
         4
         5
         6
         7

SQL>

-- 
http://www.mladen-gogala.com

Received on Tue Oct 09 2007 - 08:09:17 CDT