Re: Query to return PK sequence gap ??

On 9 Okt., 11:33, 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

Hi,

You could create a pipeline-function returning all numbers from :emp_id_from to :emp_id_to and find the missing keys with a 'not exists()'-query which would look something like:

select column_value
from table(pipe_func(:emp_id_from,:emp_id_to) where not exists
(
select null
from emp_test
where emp_id=column_value
)

Hope that helps,
stephan Received on Tue Oct 09 2007 - 06:07:12 CDT