Hi Diego,

I wrote a single sql command here to find out empty numbers but it
was not optimistic i think. ( I dont like to do heavy load FB ;) )

Try this one:

Create table lotarynumbers
(
thenumber integer not null primary key check(value between 1 and
99999)
)

CREATE GENERATOR LOTARYNUMBERS_GEN;

99999 times, insert into lotarynumbers(thenumber) values(gen_id
(LOTARYNUMBERS_GEN,1));


select thenumber as emptynumber from lotarynumbers ln
where not exists(select 1 from mylottarytable where
lot_number=ln.thenumber)
order by thenumber

Regards.
Ali

--- In firebird-support@yahoogroups.com, "Diego Rodriguez"
<drodriguez@a...> wrote:

>
>
> Hi all,
>
> I have a table LOTTERY with an integer field NUMBER that is also

primary

> key. My app will fill this table with random numbers between 0 and

99999

> everytime that is needed. When an event happens I generate a

random number

> and try to insert that number. Because it is for a lottery, there

must not

> be repeated numbers, and this is ensured by primary key. If the

random

> number is repeated, an exception "violation of primary key" is

raised, and I

> try with another random number. I know this model will have

problems when

> the table is almost full, because I will get a lot of exceptions

each time

> an insert is made, so if the exception is raised twice, I search

for a non

> used number in the table (lottery not really random, but will

work). The way

> I get this numbers is getting all numbers ordered and

programatically find

> any gap between numbers...
>
> It is posibble to get a non used number directly with a query?
>
> I'm using Firebird 1.0.3 in a Debian Linux platform.
>
> Many thanks
>
> Diego
>