Re: WANTED: SQL-Statement

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. Received on Fri Apr 18 1997 - 00:00:00 CEST