sqloracle-databasegaps-and-islandsgaps-in-data

How to missing numbers by 100s in oracle

I need to find the missing numbers in a table column in oracle, where the missing numbers must be taken by 100s , meaning that if it's found 1 number at least between 2000 and 2099 , all missing numbers between 2000 and 2099 must be returned and so on.

here is an example that clarify what I need:

create table test1 ( a number(9,0));

insert into test1 values (2001);
insert into test1 values (2002);
insert into test1 values (2004);
insert into test1 values (2105);
insert into test1 values (3006);
insert into test1 values (9410);
commit;

the result must be 2000,2003,2005 to 2099,2100 to 2104,2106 to 2199,3000 to 3005,3007 to 3099,9400 to 9409,9411 to 9499.

I started with this query but it's obviously not returning what I need :

SELECT Level+(2000-1) FROM dual  CONNECT BY LEVEL  <= 9999 
MINUS SELECT a FROM test1;
Solution

  • You can use the hiearchy query as follows:

    SQL> SELECT A FROM (
  2  SELECT A + COLUMN_VALUE - 1 AS A
  3    FROM ( SELECT DISTINCT TRUNC(A, - 2) A
  4         FROM TEST_TABLE) T
  5   CROSS JOIN TABLE ( CAST(MULTISET(
  6  SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100
  7     ) AS SYS.ODCINUMBERLIST) ) LEVELS
  8  )
  9  MINUS
 10  SELECT A FROM TEST_TABLE;

         A
----------
      2000
      2003
      2005
      2006
      2007
      2008
      2009
.....
.....