Find missing serials
Hello,
How to find a missing sequence Using oracle 8i from a table ? the purpose I need to assign available sequences automatically for customer accounts, as below example :
Account_no
1
2
4
5
8
results:
1) The result of the query is 3 , when (3) inserted in the table, query should get 6..etc.
2) Also how can I find all missing sequences , result : 3,6,7 using one select?
Thank You
How to find a missing sequence Using oracle 8i from a table ? the purpose I need to assign available sequences automatically for customer accounts, as below example :
Account_no
1
2
4
5
8
results:
1) The result of the query is 3 , when (3) inserted in the table, query should get 6..etc.
2) Also how can I find all missing sequences , result : 3,6,7 using one select?
Thank You
David Johnson, CD
Customer Accounts, Serial Numbers once assigned have an infinite lifetime. They never can be reused. If you delete them from the current database they should still exist in your data warehouse
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank u
If your sample data has a 3, your query returns 7 not 6.
This one only hits the table once:
To show all missing numbers, this is what I came up with:
There are many other options on the web.
This one only hits the table once:
select min(rn) from (
select num, row_number() over(order by num) rn from tab1
)
where num!=rn
;To show all missing numbers, this is what I came up with:
with max_num as (
select max(num) max_num from tab1
),
all_numbers as (
select rownum rn from max_num connect by level <= max_num
),
combined as (
select rn, num
from all_numbers a left join tab1 t1 on a.rn = t1.num
)
select rn from combined where num is null order by rn
;There are many other options on the web.