Re: WANTED: SQL-Statement

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/04/19
Message-ID: <3358A74C.2DD7_at_iol.ie>#1/1


Mea culpa!

   The solution I suggested only gives the first of s set of consecutive missing numbers.

   The easiest (and most efficient) method is probably to use a PL/SQL procedure like:

declare

   cursor C is slect nr from my numbers order by 1;    n number := 0; -- use your start number here begin

   for R in C
   loop

      while n < R.nr
      loop
         dbms_output.put_line(n);
         n := n+1;
      end loop;
      n := n+1;

   end loop;
end;       

Chrysalis wrote:
>
> Rohrbacher wrote:
> >
> > Frank Dugrillon wrote:
> > >
> > > Hi everbody!
> > >
> > > I am looking for a solution to the following problem:
> > >
> > > I have a table with my numbers, e.g.:
> > >
> > > SELECT * FROM MYNUMBERS;
> > > 1
> > > 3
> > > 4
> > >
> > > In another table I have all allowed values:
> > >
> > > SELECT * FROM ALLNUMBERS;
> > > 1
> > > 2
> > > 3
> > > 4
> > > 5
> > >
> > > The select statement
> > >
> > > SELECT * FROM ALLNUMBERS MINUS SELECT * FROM MYNUMBERS;
> > > 2
> > > 5
> > >
> > > returns the set of all the allowed numbers I don't use in MYNUMBERS.
> > > Does anybody see a way to get the same result without using the table
> > > ALLNUMBERS?
> > >
> > > (because I would need an ugly table consisting of 10000000 numbers; I am
> > > thinking of a statement like: SELECT * FROM "virtual set of numbers from
> > > 1 to 5" MINUS SELECT * FROM MYNUMBERS;)
> > >
> > > Thank you for any hint!
> > >
> > > Frank
> > > frank.dugrillon_at_chbs.mhs.ciba.com
> >
> > I had a similar problem, detecting missing number in a table where
> > all numbers should have been in ascending order increasing by one.
> > Luckily I had only positive integers and the table ( or any other table)
> > held enough rows to do the following statement :
> >
> > select rownum from the_table_with_enough_rows where rownum <=
> > 8000 ( or any variable if applicable )
> > minus
> > select nr from mynumbers ;
> >
> > If you are using a tool where you can pass variables to the statement
> > you can further utilies the statement.
> > e.g. (rownum * :Variable1) - :Variable2 ....
> >
> > Hope you find it worth reading.
> >
> > Ciao Robo.
>
> You can also use:
>
> select N.nr+1 MISSING from mynumbers N
> where not exists
> (select null from mynumbers N1
> where N1.nr = N.nr+1);
>
> (This includes the number following the last number in the set)
>
> Chrysalis.
Received on Sat Apr 19 1997 - 00:00:00 CEST

Original text of this message