Home » SQL & PL/SQL » SQL & PL/SQL » finding the missing sequences in a column (oracle 10g)
finding the missing sequences in a column [message #433893] Fri, 04 December 2009 22:05 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
i have sequence numbers inserted in a column with concatenated with 'INV'+sequence like this.somehow some sequences are jumped because of some exceptions in the code.i want to find out what is the minimum sequence and maximum sequnce and most importantly what are the missed sequence numbers. Is there any short way to find out missied sequences?

INV001
INV002
INVOO3
INV005
INV008
INV009
...
Re: finding the missing sequences in a column [message #433894 is a reply to message #433893] Fri, 04 December 2009 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
>Is there any short way to find out missied sequences?
I suggest you count starting at 1 and compare what is in the table.

>>INVOO3
above appear to be capital letter OH; note zeros
How did this happen?
Re: finding the missing sequences in a column [message #433895 is a reply to message #433893] Fri, 04 December 2009 22:13 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
its zeroes only.
Re: finding the missing sequences in a column [message #433899 is a reply to message #433895] Fri, 04 December 2009 22:23 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
chaituu wrote on Fri, 04 December 2009 20:13
its zeroes only.

I am not sure what you mean by "it", but in your original post they are

INVOO3
not
INV003
Re: finding the missing sequences in a column [message #433900 is a reply to message #433893] Fri, 04 December 2009 22:27 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
it should be

INV001
INV002
INV003
INV005
INV008
INV009
Re: finding the missing sequences in a column [message #433901 is a reply to message #433900] Fri, 04 December 2009 22:32 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
I suggest you practice CUT & PASTE; which does not make mistakes
Re: finding the missing sequences in a column [message #433909 is a reply to message #433893] Fri, 04 December 2009 23:18 Go to previous messageGo to next message
calluru
Messages: 17
Registered: July 2009
Location: Austin, TX, USA
Junior Member
This query generates all the ids between first and last ids, checks if all those ids are in the source table and lists the ones that are missing in the original table.

WITH test_data AS (
SELECT 'INV001' id FROM DUAL UNION
SELECT 'INV002' FROM DUAL UNION
SELECT 'INV003' FROM DUAL UNION
SELECT 'INV005' FROM DUAL UNION
SELECT 'INV006' FROM DUAL UNION
SELECT 'INV007' FROM DUAL)
SELECT id
FROM   (SELECT 'INV' || LPAD(TO_CHAR(level), 3, '0') id
                FROM   (SELECT MIN(TO_NUMBER(SUBSTR(id, 4))) minid, 
                       MAX(TO_NUMBER(SUBSTR(id, 4))) maxid 
                FROM   test_data)
        CONNECT BY LEVEL BETWEEN minid AND maxid)
WHERE  id NOT IN (SELECT id
                  FROM   test_data)
ORDER BY id;
Re: finding the missing sequences in a column [message #433914 is a reply to message #433893] Sat, 05 December 2009 00:04 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
use analytics on the numbers in the columns to look for gaps and display them how ever you like, may with a number generator that generates missing values. Here is an example

SQL> drop table t1
  2  /

Table dropped.

SQL> 
SQL> create table t1 (c1 number)
  2  /

Table created.

SQL> 
SQL> insert into t1 values (1);

1 row created.

SQL> insert into t1 values (2);

1 row created.

SQL> insert into t1 values (5);

1 row created.

SQL> insert into t1 values (6);

1 row created.

SQL> 
SQL>        select c1,lead(c1) over (order by c1) next_c1
  2                  ,nvl(lead(c1) over (order by c1) - c1,0) missing_count
  3         from t1
  4  /

        C1    NEXT_C1 MISSING_COUNT
---------- ---------- -------------
         1          2             1
         2          5             3
         5          6             1
         6                        0

SQL> 
SQL> select c1,next_c1,nvl(c1+rowno-1,c1)
  2  from (
  3         select c1,lead(c1) over (order by c1) next_c1
  4                  ,nvl(lead(c1) over (order by c1) - c1,0) missing_count
  5         from t1
  6       ) x
  7       ,(
  8          select rownum rowno
  9          from dual
 10          connect by level <= 100
 11        ) somenumbers
 12  where x.missing_count >= somenumbers.rowno(+)
 13  order by 1,3
 14  /

        C1    NEXT_C1 NVL(C1+ROWNO-1,C1)
---------- ---------- ------------------
         1          2                  1
         2          5                  2
         2          5                  3
         2          5                  4
         5          6                  5
         6                             6

6 rows selected.
SQL> 


Notice how the last column has no gaps. The number generator create a list however long you want, but if your gap is bigger, you loose rows so you need to know your biggest gap. Too bad I could not figure out how to correlate the number generator to the gap for each key. Maybe someone else can do that. Wonder which is faster?

Good luck, Kevin
Previous Topic: sequence no higher than other
Next Topic: union
Goto Forum:
  


Current Time: Sun Sep 25 11:10:04 CDT 2016

Total time taken to generate the page: 0.08422 seconds