Re: WANTED: SQL-Statement

From: Gary Myers <gary.myers_at_research.natpower.co.uk>
Date: 1997/04/21
Message-ID: <335b4efc.2826550_at_internet>#1/1

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);

Then get the end of the gap as follows : Create table TempB 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);

You can then get the gaps as follows
Select A.NUM, B.NUM from TempA, TempB
Where A.RN=B.RN

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)

...

---

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. 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 CEST