Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Query to return PK sequence gap ??
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)
2 4 5 6 7
SQL>
-- http://www.mladen-gogala.comReceived on Tue Oct 09 2007 - 08:09:17 CDT
![]() |
![]() |