Re: WANTED: SQL-Statement

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 Fri Apr 18 1997 - 00:00:00 CDT