Re: How do I retrieve free values?

If you are using integers then you could do something like this.

Using a table with at least as many rows as the max integer you can use the MINUS operator.

ops$clbeck_at_ORA8I.WORLD> select * from n;

       NUM

         1
         2
         3
         4
         5
         7
         8
        10

8 rows selected.

ops$clbeck_at_ORA8I.WORLD> select rownum from all_objects where rownum < 11   2 minus
  3 select num from n
  4 /

    ROWNUM

         6
         9

hope this helps.

chris.

--
Christopher Beck, Principal Technologist, Oracle Corporation,
christopher.beck_at_oracle.com
Beginning Oracle Programming,
http://www.amazon.com/exec/obidos/ASIN/186100690X



"Lars Reineke" <reineke_at_kreiskrankenhaus-hameln.de> wrote in message
news:ak26or$1ev86v$1_at_ID-1759.news.dfncis.de...


> Hi!

>

> Given a table A with a column NUMBER, which stores numerical values, let's

> say in a range from 1 to 9999, how do I retrieve the values that are in

that


> range but not stored into that table?

>

> NUMBER

> --------------

> 1

> 2

> 3

> 4

> 5

> 7

> 8

> 10

>

> I would like to get the not assigned 6, the 9 and so on.

>

> Many thanks in advance.

>

> Greetings

> Lars

>

>