Home » SQL & PL/SQL » SQL & PL/SQL » Missing Numbers (10g, xp)
Missing Numbers [message #410748] Tue, 30 June 2009 01:38 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
i have a table with following values

100
101
102
104
105
106
108

How Can I find the missing nos with SELECT statement?
i.e
103
107

Please guide me...

RzKhan

Re: Missing Numbers [message #410750 is a reply to message #410748] Tue, 30 June 2009 02:04 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
You can use lag function and add 1 to it. If this new value is different then the current value then that number is missing number. However this method will not be able to list all the missing numbers within the gap, if the gap is greater than 1

[Updated on: Tue, 30 June 2009 02:05]

Report message to a moderator

Re: Missing Numbers [message #410751 is a reply to message #410748] Tue, 30 June 2009 02:07 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As such a question has already been asked (quite frequently, actually), perhaps you'd find some ideas while searching the board.
Re: Missing Numbers [message #410752 is a reply to message #410750] Tue, 30 June 2009 02:08 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member

As my requirement I have to list ALL missing numbers, so lag function (as you said) lacks the functionality.

RZKhan
Re: Missing Numbers [message #410755 is a reply to message #410752] Tue, 30 June 2009 02:15 Go to previous messageGo to next message
ds285269
Messages: 10
Registered: June 2009
Location: Mumbai
Junior Member
For this we will done by collect all the data into one cursor and create one for loop with the range of min and max numbers in our data, with in that loop we will comare each and every number with the cursor numbers any one we found strange just pritn that one....,
I hope it's work fine...
Re: Missing Numbers [message #410756 is a reply to message #410748] Tue, 30 June 2009 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's one way: Use a row generator (search for this) to generate numbers between min and max and remove (minus) those you have in your table.

Regards
Michel
Re: Missing Numbers [message #410758 is a reply to message #410756] Tue, 30 June 2009 02:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel read my mind. I'd opt for a row generator (probably the old 'dual...connect by' construction).

Key is: you have to list the numbers between the smallest and the largest value of your table and filter out those that already exist in the table. That leaves you with all the missing values.

MHE
Re: Missing Numbers [message #410887 is a reply to message #410758] Tue, 30 June 2009 23:21 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
When I try to generate numbers between two numbers, It gives only 1 row.
SQL> ;
  1  SELECT ROWNUM AS rn
  2    FROM   dual
  3*  CONNECT BY ROWNUM < 10000
SQL> /

       RN
---------
        1
        2
        3
        4
        5
        6
.
.
.
.

SQL> ed
Wrote file afiedt.buf

  1  SELECT ROWNUM AS rn
  2    FROM   dual
  3*  CONNECT BY ROWNUM between 10 and 20
SQL> /

       RN
---------
        1

RzKhan

[Updated on: Tue, 30 June 2009 23:24] by Moderator

Report message to a moderator

Re: Missing Numbers [message #410890 is a reply to message #410887] Tue, 30 June 2009 23:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL Reference
Chapter 3 Pseudocolumns
Section ROWNUM Pseudocolumn

Regards
Michel
Re: Missing Numbers [message #411093 is a reply to message #410748] Wed, 01 July 2009 22:14 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
If I choose to use the nested loops to solve the problem.
I wonder why the I can't have the desired result.

create table test (cardno number(5));



insert into test values (80001);
insert into test values (80004);



SQL> select * from test;

   CARDNO
---------
    80001
    80004

SQL> ed
Wrote file afiedt.buf

  1   create or replace procedure m
  2     as
  3     cursor c1 is select cardno from test order by cardno;
  4     minCard number(6);
  5     maxCard number(6);
  6      begin
  7      select min(cardno) into minCard from test;
  8      select max(cardno) into maxCard from test;
  9   for i in 80001..80004 loop
 10      for x in c1 loop
 11         if i=x.cardno then
 12           dbms_output.put_line(i);
 13         end if;
 14      end loop;
 15   end loop;
 16*    end;
SQL> /

Procedure created.

SQL> exec m;
80001
80004


and if I reverse the condition at line 11 then (I think, which may be wrong) I should get the the (missing) numbers.

80002
80003




But it does not.

SQL> ed
Wrote file afiedt.buf

  1   create or replace procedure m
  2     as
  3     cursor c1 is select cardno from test order by cardno;
  4     minCard number(6);
  5     maxCard number(6);
  6      begin
  7      select min(cardno) into minCard from test;
  8      select max(cardno) into maxCard from test;
  9   for i in 80001..80004 loop
 10      for x in c1 loop
 11          if i<>x.cardno then
 12            dbms_output.put_line(i);
 13          end if;
 14      end loop;
 15   end loop;
 16*    end;
SQL> /

Procedure created.

SQL> exec m;
80001
80002
80002
80003
80003
80004

PL/SQL procedure successfully completed.

SQL> 




any suggestion!


RzKhan


[Updated on: Wed, 01 July 2009 22:18]

Report message to a moderator

Re: Missing Numbers [message #411094 is a reply to message #410748] Wed, 01 July 2009 22:36 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I wonder why the I can't have the desired result.
You choose to use the nested loops to solve the problem incorrectly.
Re: Missing Numbers [message #411096 is a reply to message #410755] Wed, 01 July 2009 22:47 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
ds285269 wrote on Tue, 30 June 2009 03:15
For this we will done by collect all the data into one cursor and create one for loop with the range of min and max numbers in our data, with in that loop we will comare each and every number with the cursor numbers any one we found strange just pritn that one....,
I hope it's work fine...



As there are many approaches, I just want to test the above one.

RzKhan

[Updated on: Wed, 01 July 2009 22:50]

Report message to a moderator

Re: Missing Numbers [message #411097 is a reply to message #411096] Wed, 01 July 2009 22:59 Go to previous messageGo to next message
ds285269
Messages: 10
Registered: June 2009
Location: Mumbai
Junior Member
DECLARE
flag NUMBER;
min_val NUMBER;
max_val NUMBER;
CURSOR c1 IS
SELECT NO
FROM aaa;
i NUMBER;
BEGIN
SELECT Min(NO)
INTO min_val
FROM aaa;

SELECT Max(NO)
INTO max_val
FROM aaa;

FOR i IN min_val.. max_val LOOP
flag := 1;

FOR j IN c1 LOOP
IF j.NO = i THEN
flag := 0;

EXIT;
END IF;
END LOOP;

IF flag = 1 THEN
dbms_output.Put_line(i);
END IF;
END LOOP;
END;
Re: Missing Numbers [message #411098 is a reply to message #410748] Wed, 01 July 2009 23:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>As there are many approaches, I just want to test the above one.
Problem is when results are not as desired, how can you conclude if the failure is because approach is flawed or implementation was flawed?

All you can conclude is that your test did not produce desired results.

How do you differentiate between flawed approach & flawed implementation? Both produce not desired results.
Re: Missing Numbers [message #411099 is a reply to message #410748] Wed, 01 July 2009 23:10 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
ds285269,
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

DECLARE
  flag     NUMBER;
   min_val  NUMBER;
   max_val  NUMBER;
   CURSOR c1 IS
     SELECT NO
     FROM   aaa;
   i        NUMBER;
BEGIN
  SELECT Min(NO)
  INTO   min_val
  FROM   aaa;
  
  SELECT Max(NO)
  INTO   max_val
  FROM   aaa;
  
  FOR i IN min_val.. max_val LOOP
    flag := 1;
    
    FOR j IN c1 LOOP
      IF j.NO = i THEN
        flag := 0;
        
        EXIT;
      END IF;
    END LOOP;
    
    IF flag = 1 THEN
      dbms_output.Put_line(i);
    END IF;
  END LOOP;
END; 
Re: Missing Numbers [message #411102 is a reply to message #411098] Wed, 01 July 2009 23:13 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
implementation Sad

SQL> create or replace procedure uz
  2  as
  3  flag NUMBER;
  4  min_val NUMBER;
  5  max_val NUMBER;
  6  CURSOR c1 IS
  7  SELECT cardno FROM test order by cardno;
  8  i NUMBER;
  9  BEGIN
 10  SELECT Min(cardno) INTO min_val FROM test;
 11  SELECT Max(cardno) INTO max_val FROM test;
 12  FOR i IN min_val.. max_val LOOP
 13  flag := 1;
 14  FOR j IN c1 LOOP
 15  IF j.cardno = i THEN
 16  flag := 0;
 17  EXIT;
 18  END IF;
 19  END LOOP;
 20  IF flag = 1 THEN
 21  dbms_output.Put_line(i);
 22  END IF;
 23  END LOOP;
 24  END;
 25  /

Procedure created.

SQL> select * from test;

   CARDNO
---------
    80001
    80004
    80010

SQL> exec uz;
80002
80003
80005
80006
80007
80008
80009

PL/SQL procedure successfully completed.

SQL> 



good day
Re: Missing Numbers [message #411135 is a reply to message #411102] Thu, 02 July 2009 02:25 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I didn't quite get why PL/SQL instead of SQL; however, here's one way to do that:
select lvl 
from (select level lvl
      from dual
      connect by level <= (select max(cardno) from test)
     )
where lvl >= (select min(cardno) from test)
minus
select cardno from test;
Re: Missing Numbers [message #411139 is a reply to message #411135] Thu, 02 July 2009 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Isn't what I said 2 days ago?
Except that I implied to generate only numbers between min and max and not all numbers up to max.

Regards
Michel
Re: Missing Numbers [message #411152 is a reply to message #410748] Thu, 02 July 2009 03:05 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
YES.


and below is another variation of the code.

SQL> ed
Wrote file afiedt.buf

  1   create or replace procedure q1
  2   as
  3   v_count number;
  4   begin
  5   for a in 80001 .. 80010 loop
  6   select count(*) into v_count from test where cardno = a;
  7   if v_count=0 then
  8  ----- dbms_output.put_line (v_count||'-'||a);
  9  dbms_output.put_line (a);
 10  ---insert into missingCardNo values(a);
 11   end if;
 12   end loop;
 13* end;
SQL> /

Procedure created.

SQL> exec q1;
80002
80003
80005
80006
80007
80008
80009

PL/SQL procedure successfully completed.

SQL> select * from test;

   CARDNO
---------
    80001
    80004
    80010



rzkhan
Re: Missing Numbers [message #411160 is a reply to message #411152] Thu, 02 July 2009 03:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Congratulations, I think you just discovered the slowest way to do this Wink
Re: Missing Numbers [message #411166 is a reply to message #411160] Thu, 02 July 2009 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank wrote on Thu, 02 July 2009 10:29
Congratulations, I think you just discovered the slowest way to do this Wink

Sure! Laughing
Re: Missing Numbers [message #411192 is a reply to message #411139] Thu, 02 July 2009 06:50 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 02 July 2009 09:31
I implied to generate only numbers between min and max and not all numbers up to max.


Something like this? Or did you have something else on mind?
select min(t.cardno) + x.lvl - 1
from test t, (select level lvl
              from dual
              connect by level <= (select max(cardno) - min(cardno) from test)
             ) x            
group by x.lvl
minus
select cardno from test
order by 1;
Re: Missing Numbers [message #411202 is a reply to message #411192] Thu, 02 July 2009 07:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'd not write it this way but this is what I had in mind.

Regards
Michel
Re: Missing Numbers [message #411207 is a reply to message #411202] Thu, 02 July 2009 07:55 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How would you write it, then?
Re: Missing Numbers [message #411220 is a reply to message #411207] Thu, 02 July 2009 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
with minmax as (select min(col) mini, max(col) maxi from t)
select mini+level-1
from minmax
connect by level <= maxi-mini
minus
select col from t
/

Regards
Michel

[Updated on: Thu, 02 July 2009 08:55]

Report message to a moderator

Re: Missing Numbers [message #411253 is a reply to message #411220] Thu, 02 July 2009 15:55 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ah, nice! Thank you!
Previous Topic: Inserting values from lower precision to higher
Next Topic: Multiple Search (merged 2) - 10g
Goto Forum:
  


Current Time: Sat Dec 10 13:10:42 CST 2016

Total time taken to generate the page: 0.09933 seconds