This discussion is archived
10 Replies Latest reply: Jan 2, 2013 9:30 PM by user9077620 RSS

FInding missing sequence number

user9077620 Newbie
Currently Being Moderated
Dear All

I have a column in table like this (actually its just a sample):

TAB
MMxxxx9988
MMxxxx9990
MMxxxx9995
MMxxxx9998
MMxxxx9999
MMxxxx0000
MMxxxx0001
MMxxxx0003
MMxxxx0004
MMxxxx0005
MMxxxx0008



xxxx is variable
last four digit is a sequence number start with 0000 and end with 9999.
when the last four digit exceeds 9999 it reset to 0000

My question is : how can we find the missing sequence number for that column.

example:
for the above table , it should give the below result:
TAB
MMxxxx9989
MMxxxx9991
MMxxxx9992
MMxxxx9993
MMxxxx9994
MMxxxx9996
MMxxxx9997
MMxxxx0002
MMxxxx0006
MMxxxx0007

Thanks
  • 1. Re: FInding missing sequence number
    jeneesh Guru
    Currently Being Moderated
    select lpad(to_char(level-1),4,'0') seq
    from dual
    connect by level <= 10000
    minus
    select substr(your_column,-4)
    from your_table
  • 2. Re: FInding missing sequence number
    user9077620 Newbie
    Currently Being Moderated
    Dear jeneesh

    Thanks for your reply,
    the query that you wrote show all data between 0000 and 9999 except the one that exist in the column.

    but I need to show only between the data in the first columns.

    thanks
  • 3. Re: FInding missing sequence number
    Purvesh K Guru
    Currently Being Moderated
    One another way of doing it:
    For ease of verification, I have removed the records from 0001 to 9988.
    with data as
    (
      select 'MMxxxx9988' col from dual union all
      select 'MMxxxx9990' col from dual union all
      select 'MMxxxx9995' col from dual union all
      select 'MMxxxx9998' col from dual union all
      select 'MMxxxx9999' col from dual
    --  select 'MMxxxx0000' col from dual union all
    --  select 'MMxxxx0001' col from dual union all
    --  select 'MMxxxx0003' col from dual union all
    --  select 'MMxxxx0004' col from dual union all
    --  select 'MMxxxx0005' col from dual union all
    --  select 'MMxxxx0008' col from dual
    )
    select distinct col, nxt_val, substr(col, 1, 6) || to_char(st_with + level, 'fm00009') miss_seq
      from (
            select col, nxt_val, col_num st_with, nxt_col_num end_with
              from (
                    select col, lead(col) over (partition by substr(col, 1, 6) order by substr(col, -4, 4)) nxt_val,
                           to_number(substr(col, 7, 4)) col_num, to_number(substr(lead(col) over (partition by substr(col, 1, 6) order by substr(col, -4, 4)), 7, 4)) nxt_col_num
                      from data
                   )
             where abs(col_num - nxt_col_num) > 1
           )
    connect by level <= (end_with - st_with) - 1
       and prior col = col
       and prior sys_guid() is not null
    order by col;
    
    COL        NXT_VAL    MISS_SEQ     
    ---------- ---------- ------------ 
    MMxxxx9988 MMxxxx9990 MMxxxx09989  
    MMxxxx9990 MMxxxx9995 MMxxxx09991  
    MMxxxx9990 MMxxxx9995 MMxxxx09992  
    MMxxxx9990 MMxxxx9995 MMxxxx09993  
    MMxxxx9990 MMxxxx9995 MMxxxx09994  
    MMxxxx9995 MMxxxx9998 MMxxxx09996  
    MMxxxx9995 MMxxxx9998 MMxxxx09997  
    
     7 rows selected
  • 4. Re: FInding missing sequence number
    user9077620 Newbie
    Currently Being Moderated
    Dear Purvesh

    Thanks for your reply,
    your query also didn't give the correct result,
    because what i need is :
    when we look at the first column which start with MMxxxx9988 and end with MMxxxx9999, there are 7 values are missing which are MMxxxx9989,
    MMxxxx9991,
    MMxxxx9992,
    MMxxxx9993,
    MMxxxx9994,
    MMxxxx9996,
    MMxxxx9997

    and the rest one which start with MMxxxx0000 and end with MMxxxx0008 ,there are3 values are missing which are MMxxxx0002
    MMxxxx0006
    MMxxxx0007


    the final result will be:
    MMxxxx9989
    MMxxxx9991,
    MMxxxx9992
    MMxxxx9993
    MMxxxx9994
    MMxxxx9996
    MMxxxx9997
    MMxxxx0002
    MMxxxx0006
    MMxxxx0007



    Thanks
  • 5. Re: FInding missing sequence number
    KeithJamieson Expert
    Currently Being Moderated
    Here is a way.
    with mytab
    as
    (
    select to_number(substr('MMxxxx9988',7)) mylist from dual union all
    select to_number(substr('MMxxxx9990',7))  from dual union all
    select to_number(substr('MMxxxx9995',7))  from dual union all
    select to_number(substr('MMxxxx9998',7)) from dual union all
    select to_number(substr('MMxxxx9999',7)) from dual union all
    select to_number(substr('MMxxxx0000',7)) from dual union all
    select to_number(substr('MMxxxx0001',7)) from dual union all
    select to_number(substr('MMxxxx0003',7)) from dual union all
    select to_number(substr('MMxxxx0004',7)) from dual union all
    select to_number(substr('MMxxxx0005',7)) from dual union all
    select to_number(substr('MMxxxx0008',7)) from dual),
    mynumbers 
    as
    (select 9987+rownum mylist
      from dual 
    connect by level <= (1000-988)
    union 
    select -1+rownum mylist
      from dual 
    connect by level <= 9
    )
    select 'MMxxxx'||mylist
    from
    (
    select lpad(mylist,'4','0') mylist from mynumbers
    minus
    select lpad(mylist,'4','0') mylist  from mytab
    )
    and my results are:
    MMxxxx0002
    MMxxxx0006
    MMxxxx0007
    MMxxxx9989
    MMxxxx9991
    MMxxxx9992
    MMxxxx9993
    MMxxxx9994
    MMxxxx9996
    MMxxxx9997
  • 6. Re: FInding missing sequence number
    Purvesh K Guru
    Currently Being Moderated
    Your Original Statements are as follows:
    xxxx is variable
    last four digit is a sequence number start with 0000 and end with 9999.
    when the last four digit exceeds 9999 it reset to 0000

    My question is : how can we find the missing sequence number for that column.
    Original Data:-
    TAB
    MMxxxx9988
    MMxxxx9990
    MMxxxx9995
    MMxxxx9998
    MMxxxx9999
    MMxxxx0000
    MMxxxx0001
    MMxxxx0003
    MMxxxx0004
    MMxxxx0005
    MMxxxx0008
    After MMxxxx0008, the next sequence is MMxx0088. Logically, I can draw a conclusion that there are missing sequences from MMxxxx0009 to MMxxxx9987, can't I?
  • 7. Re: FInding missing sequence number
    user9077620 Newbie
    Currently Being Moderated
    I meant the missing sequence within the given data,not the outer of it.

    Thanks
  • 8. Re: FInding missing sequence number
    Peter vd Zwan Expert
    Currently Being Moderated
    Hi,

    You have to include an additional collumn with the sequence of your values.
    Remember the rows in a table are not in any particular order, so if you do not show where to start or stop we can only asume you wanted to have all missing from 0000 to 9999.

    Regards,

    Peter
  • 9. Re: FInding missing sequence number
    Peter vd Zwan Expert
    Currently Being Moderated
    Hi,

    With a seq column:
    with a as
    (
    select 1 seq, 'MMxxxx9988' col from dual union all
    select 2 seq, 'MMxxxx9990' col from dual union all
    select 3 seq, 'MMxxxx9995' col from dual union all
    select 4 seq, 'MMxxxx9998' col from dual union all
    select 5 seq, 'MMxxxx9999' col from dual union all
    select 6 seq, 'MMxxxx0000' col from dual union all
    select 7 seq, 'MMxxxx0001' col from dual union all
    select 8 seq, 'MMxxxx0003' col from dual union all
    select 9 seq, 'MMxxxx0004' col from dual union all
    select 10 seq, 'MMxxxx0005' col from dual union all
    select 11 seq, 'MMxxxx0008' col from dual
    )
    ,b as
    (
    select
      seq
      ,col
      ,case when max( to_number(substr(col,7)) ) over (partition by 1 order by seq rows between unbounded preceding and 1 preceding) > to_number(substr(col,7))
            then 10000 + to_number(substr(col,7)) 
            else to_number(substr(col,7)) 
            end num
    from
      a
    )
    ,c as
    (
    select
      min(num) min_num
      ,max(num) max_num
      ,min(substr(col,1,6)) prefix
    from
      b
    )
    select
      prefix || trim( to_char(mod(level + c.min_num,10000),'0000')) col
    from
      c
    connect by
      level < max_num - min_num
    minus
    select
      col
    from
      a
    
    ;
    COL       
    -----------
    MMxxxx0002  
    MMxxxx0006  
    MMxxxx0007  
    MMxxxx9989  
    MMxxxx9991  
    MMxxxx9992  
    MMxxxx9993  
    MMxxxx9994  
    MMxxxx9996  
    MMxxxx9997  
    
     10 rows selected 
    Regards,

    Peter
  • 10. Re: FInding missing sequence number
    user9077620 Newbie
    Currently Being Moderated
    Dear All,

    Thanks for you all.

    Done,
    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points