10 Replies Latest reply: Jan 2, 2013 11:30 PM by user9077620 RSS

    FInding missing sequence number

    user9077620
      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
          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
            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
              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
                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
                  Keith Jamieson
                  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
                    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
                      I meant the missing sequence within the given data,not the outer of it.

                      Thanks
                      • 8. Re: FInding missing sequence number
                        Peter vd Zwan
                        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
                          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
                            Dear All,

                            Thanks for you all.

                            Done,
                            Thanks