Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Naveen.

Asked: November 23, 2015 - 3:49 pm UTC

Last updated: November 25, 2015 - 11:38 pm UTC

Version: 10

Viewed 1000+ times

You Asked

Hello Experts,

I have a requirement which I need to find out the missing sequence numbers

Order No
20001200
20001201
20001202
20001203
20001204
20001205
20001206
20001209
20001210
20001212
20001214
20001218

missing numbers are deleted orders and my output should be like below

Output
Order No
20001199
20001207
20001208
20001211
20001213
20001215
20001216
20001217

Please help me.

Thanks,
Naveen

and Chris said...

One way is to generate all the possible values between the lowest possible and the maximum. Then exclude from this list the rows that exist in the table.

For example:

create table t ( order_no int );

insert into t values (20001200);
insert into t values (20001201);
insert into t values (20001202);
insert into t values (20001203);
insert into t values (20001204);
insert into t values (20001205);
insert into t values (20001206);
insert into t values (20001209);
insert into t values (20001210);
insert into t values (20001212);
insert into t values (20001214);
insert into t values (20001218);

with rws as (
  select 20001200 + rownum - 1 r from dual
  connect by level <= (select max(order_no)-min(order_no) from t)
)
  select r from rws
  where  not exists (select * from t where rws.r = t.order_no)
  order  by r;

         R
----------
  20001207
  20001208
  20001211
  20001213
  20001215
  20001216
  20001217

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Naveen Bhogaraju, November 23, 2015 - 4:07 pm UTC

Thank you Chris for the fast response but 20001199 is missing from your output

Naveen Bhogaraju, November 23, 2015 - 4:13 pm UTC

Sorry Chris. My mistake. It's working fine.

Thank you so much.

Another possibility

Jeff, November 25, 2015 - 9:30 pm UTC

I have often looked for the perfect opportunity to use Pipelined Table Functions.

Perhaps that would work for this?

http://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm

Chris Saxon
November 25, 2015 - 11:38 pm UTC

Certainly. Pipeline functions are useful in all sorts of places.