Re: recycling numbers from db

smkirby_at_my-deja.com wrote:

> In article <39197F77.5DFD69C1_at_pestilence.net>,
> Job <Job_at_pestilence.net> wrote:
> > Dominick Vansevenant wrote:
> >
> > > Hello,
> > >
> > > I have to get a value that is not in a table, how can I do this?
> > > The purpose is to recycle numbers, e.g.:
> > >
> > > table content:
> > >
> > > 1
> > > 3
> > > 4
> > > 5
> > > 7
> > > 9
> > > 10
> > >
> > > I want the first number that is not in, thus I want 2 as result.
> > > Is this possible?
> > >
> > > Thanks in advance,
> > >
> > > Dominick
> >
> > What I'd do is scan the ordered table for each value in turn, on the
> > first
> > no-data-found I'd use that number next - you can
> > scan in numerical sequence - right?
> > for unused_value in 1..limit loop
> > begin
> > select val from table where val = unused_value;
> > exception
> > when no_data_found then exit;
> > end;
> > end loop;
> >
> > insert into blah values(unused_value);
> >
> > HTH
> >
> On the right track...try this version, which eliminates breaking out in
> the middle of a loop:
>
> begin
> for unused_value in 1..limit loop
> select val from table where val = unused_value;
> end loop;
>
> exception
> when no_data_found then
> insert into blah values(unused_value);
> end;
>

That's neater, you might want an 'order by' on the select also... Received on Thu May 11 2000 - 00:00:00 CDT