Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: WANTED: SQL-Statement
Frank Dugrillon wrote:
> 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 100000 numbers; I am
> thinking of a statement like:SELECT * FROM "virtual set of numbers from
> 1 to 5" MINUS SELECT * FROM MYNUMBERS;)
>
Don't know of any way to get this 'virtual set' of numbers.
However, depeneding on your needs, you may be able to get away
with a query detailing the gaps in the MYNUMBERS table. (Similar
methods to that posted by chyrsalis)
Get the start of the gap as follows :
Create table TempA as
Select Rownum RN, A.NUM+1 NUM from MYNUMBERS A
where not exists
(Select 1 from MYNUMBERS B Where B.Num = A.num+1);
(Select 1 from MYNUMBERS B Where B.Num = A.num-1);
You'd get a result saying, for example
NUM NUM
--- ---
30 35 (ie numbers 30-35 inclusive were unused) 39 39 (ie number 39 was unused)...
This represents the view of the author alone and is not to be considered as the position of National Power PLC. gary.myers_at_natpower.com
This represents the view of the author alone and is not to be considered as the position of National Power PLC. Received on Mon Apr 21 1997 - 00:00:00 CDT