How to get available IDs?

From: Thorsten Kettner <thorsten_kettner_at_my-deja.com>
Date: Tue, 16 Jan 2001 15:08:00 GMT
Message-ID: <941o49$njk$1@nnrp1.deja.com>

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?

Sent via Deja.com
http://www.deja.com/ Received on Tue Jan 16 2001 - 09:08:00 CST