How to missing numbers by 100s in oracle

Question

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;

Is there a logic to determine the lowest(2000) and uppermost(9499) values..? — Barbaros Özhan, Nov 06 '20 at 11:08

score 2 · Accepted Answer · answered Nov 06 '20 at 09:35

2

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
.....
.....

answered Nov 06 '20 at 09:35

Popeye

35,427
4
10
31

You don't need the table collection expression and can just `CROSS JOIN` the query. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=9b3dc6a95afc6ce315cb848878ec1442) – MT0 Nov 06 '20 at 14:15

GMB · Answer 2 · 2020-11-06T16:07:29.803

I like to use standard recursive queries for this.

with nums (a, max_a) as (
    select min(a), max(a) from test1
    union all 
    select a + 1, max_a from nums where a < max_a
)
select n.a
from nums n
where not exists (select 1 from test1 t where t.a = n.a)
order by n.a

The with clause takes the minimum and maximum value of a in the table, and generates all numbers in between. Then, the outer query filters on those that do not exist in the table.

If you want to generate ranges of missing numbers instead of a comprehensive list, you can use window functions instead:

select a + 1 start_a, lead_a - 1 end_a
from (
    select a, lead(a) over(order by a) lead_a
    from test1
) t
where lead_a <> a + 1

Demo on DB Fiddle

EDIT:

If you want the missing values within ranges of thousands, then we can slightly adapt the recursive solution:

with nums (a, max_a) as (
    select distinct floor(a / 100) * 100 a, floor(a / 100) * 100 + 100 from test1
    union all 
    select a + 1, max_a from nums where a < max_a
)
select n.a
from nums n
where not exists (select 1 from test1 t where t.a = n.a)
order by n.a

I've used your first example to solve a leetcode question ;) — downvoteit, Nov 18 '22 at 10:57

Barbaros Özhan · Answer 3 · 2020-11-06T11:38:04.633

Assuming you define fixed upper and lower bound for the range, then just need to eliminate the results of the current query by use of NOT EXISTS such as

SQL> exec :min_val:=2000
SQL> exec :min_val:=2499
SQL> SELECT *
       FROM
       (
        SELECT level + :min_val - 1 AS nr
          FROM dual        
       CONNECT BY level <= :max_val - :min_val + 1
       )
      WHERE NOT EXISTS ( SELECT * FROM test1 WHERE a = nr ) 
      ORDER BY nr;
      /

Demo

How to missing numbers by 100s in oracle

3 Answers3