How to find out next missing sequence number
Hi All,
I have a table loan. This has a primary key ACCNUM, which is a sequence number with 7 digits (but not oracle sequence, last used number is maintained in a separate table).
Number that begins with 9% are Current accounts. (9000000...etc). There may be gaps in this sequence.
I want to reuse this number in creating new records.
To find out the missing numbers, I used the below query.
" select accnum+1
from (
select accnum, lead(accnum) over ( order by accnum ) next_accnum
from loan WHERE accnum LIKE '9%'
)
where accnum+1 <> next_accnum
and rownum = 1;"
My first number in the sequence is 9001587. So my first unused number should be 9000000.
Whereas the above query shows me 9001588.
Can anyone help me in finding it out.