Hi Daniel,
your perfect design needs a perfect world. In a company people come and
go and some day you might wish to re-use the employee numbers of those
who have gone. And after all, this is just an example. In an elder
company you will find a lot of ID tables with gaps; for delivery terms,
payment conditions, packing types and so on.
By the way: our system is always used. I would have to stop access to
it to reorganize things and I wouldn't do so for such a reason.
Thank you anyhow.
In article <3A653228.64416391_at_exesolutions.com>,
"Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote:
> > Hi, I think this is a beginner's question: I want to get all
available
> > employee numbers by sql to put new employees into the system. If
emp_no
> > is NUMBER(3) then this is how I do it:
> >
> > SELECT rownum - 1 FROM very_big_table WHERE rownum <= 1000
> > MINUS
> > SELECT emp_no FROM emp
> >
> > The problem is that I need that very_big_table to do the trick. If I
> > want to to the same for article numbers NUMBER(6) for example, that
> > very_big_table had to be even bigger!
> >
> > Is there another simple way I just haven't thought of? I've thought
of
> > MIN and MAX from emp, but they don't help as 0 and 999 might be
used,
> > whereas the other numbers aren't. I have thought about sequences
also,
> > but a sequence doesn't know about which numbers are still in use,
so it
> > doesn't help either. Any ideas?
>
> Rownum is absolutely out. Rownum is assigned by Oracle on-the-fly and
> depending upon the query any row can have any number. If MIN() and MAX
()
> are out it is pretty obvious that you have a flawed design and are
fighting
> Oracle rather than working with it.
>
> Here's what you should do.
>
> 1. Late on Friday night or over the weekend when the system is not
being
> used renumber everyone beginning with the number 1.
>
> 2. Create a sequence, an Oracle database object, from which all
subsequent
> numbers are generated. Start the sequence with the first number above
the
> last one assigned.
>
> 3. In the future obtain all numbers during your insert statement with
SQL
> such as the following:
>
> INSERT INTO emp
> (emp_no)
> VALUES
> (next_emp_no_sequence.nextval);
>
> Daniel A. Morgan
>
>
Sent via Deja.com
http://www.deja.com/
Received on Thu Jan 18 2001 - 06:49:46 CST
