Wednesday, July 31, 2013

Oracle Tip: Find Missing Sequence No from a Table

Problem : Finding Missing Sequence No. from a Table

Solution : Using Analytics is the best solution. For this problem I have used LAG()


Steps :

  • Run the statement given below
    • TABLE1 - Table for which you need to find the missing seqno.
    • seqno- Sequence no field


    SELECT last_expected_seq as MissingSeq
    FROM (
      SELECT seqno,
             seqno- 1 as last_expected_seq,
             lag(seqno) over (order by seqno) as last_sequence
      FROM TABLE1
    ) t
    WHERE last_expected_seq <> last_sequence;


  1.                 

Digg Google Bookmarks reddit Mixx StumbleUpon Technorati Yahoo! Buzz DesignFloat Delicious BlinkList Furl

0 comments : on " Oracle Tip: Find Missing Sequence No from a Table "