Monday 31 August 2015

Find out Missing Numbers in a sequence

Find out Missing Numbers in a sequence


Example:
We have a table ORDH, which is having a field ORDERNO which starts from 300000. currently there are around 100 thousand records exists in this table.
The ORDERNO value is generated from a sequence.
We want to find out the missing (hole in the sequence) ORDERNO from the ORDH table. 
(Ex: 300654 is missing and there are lot of other order numbers were also missing)
Is there a way to find out all the missing order numbers from this table?

And We Said....

WITH     got_next_orderno     AS
(
     SELECT     orderno
     ,     LEAD (orderno) OVER (ORDER BY orderno)     AS next_orderno
     FROM     ordh
)
SELECT     ordrno + 1          AS from_no
,     next_orderno - 1     AS to_no
FROM     got_next_orderno
WHERE     orderno     + 1     != next_orderno
;


OR you can use this code as well:

select min_a - 1 + level
      from ( select min(id) min_a
                  , max(id) max_a
               from d3
           )
    connect by level <= max_a - min_a + 1
     minus
     select id
       from d3

No comments:

Post a Comment

Show values in right side of shuttle

While working with select list and shuttle, when we want to display values into right side of shuttle depending upon selection from select ...