Available numbers not in a range

mickhughes · February 23rd, 2009, 09:55 AM

Hi,
I have a Oracle 8 table with a varchar(3) primary key field that contains the numbers in the range from 0 to 999. Not all numbers are used in this range.

I need to present the user with a list of number that haven't been taken.

What is the most efficient way of doing this

Thanks

e.g.
CourseNo
001
002
003
004
008
010

Present user with
005
006
007
009

debasisdas · March 5th, 2009, 05:05 AM

the following is a simple solution.

Code:

select rownum from dba_tab_cols where rownum <=1000
minus
select code_num from table1

better to write a procedure for the purpose.