2 Replies Latest reply on Aug 19, 2009 11:51 AM by 712243

    Finding missing sequence

    712243
      Hello all.

      I'm using the Oracle 10g Database. i'm trying to figure out how to write a simple sql query to:

      find the missing numbers in a table between say 86002895 and 86005197 (inclusive)

      Ex: Current Scenario : table_1 :

      tracking_no | id_value
      86002895 | 10
      86002896 | 10
      86002899 | 10
      86002900 | 10
      86002910 | 10
      86005196 | 10
      86005197 | 10

      Expected Result1:

      " missing tracking_id " where id_value = 10 from table_1 ;

      86002897
      86002898
      86002900 to 86002910
      86002910 to 86005196

      Thanks in advance :)
        • 1. Re: Finding missing sequence
          630199
          user8635888 wrote:

          Ex: Current Scenario : table_1 :

          tracking_no | id_value
          86002895 | 10
          86002896 | 10
          86002899 | 10
          86002900 | 10
          86002910 | 10
          86005196 | 10
          86005197 | 10

          Expected Result1:

          " missing tracking_id " where id_value = 10 from table_1 ;

          86002897
          86002898
          86002900 to 86002910
          86002910 to 86005196

          Thanks in advance :)
          Maybe something like the following:

          SQL> SELECT * FROM TEST_TAB
            2  /
          
          TRACKING_NO   ID_VALUE
          ----------- ----------
             86002895         10
             86002896         10
             86002899         10
             86002900         10
             86002910         10
             86005196         10
             86005197         10
          
          7 rows selected.
          
          SQL> SELECT   CASE
            2              WHEN tracking_no + 1 = lead_no - 1 THEN TO_CHAR (tracking_no +1)
            3              ELSE TO_CHAR (tracking_no + 1) || '-' || TO_CHAR (lead_no - 1)
            4           END
            5              Missing_track_no
            6    FROM   (SELECT   tracking_no,
            7                     LEAD (tracking_no, 1, NULL)
            8                        OVER (ORDER BY tracking_no ASC)
            9                        lead_no
           10              FROM   test_tab
           11             WHERE   ID_VALUE = 10)
           12   WHERE   lead_no != tracking_no + 1
           13  /
          
          MISSING_TRACK_NO
          --------------------------------------
          
          86002897-86002898
          86002901-86002909
          86002911-86005195
          
          3 rows selected.
          
          SQL>
          You can tweak the above query to match your requirement.

          Hope this helps.

          Regards,
          Jo
          • 2. Re: Finding missing sequence
            712243
            Excellent Solution.

            Just what I was working for.

            Thanks John :)