Re: creating numbers from null values withing a set range already being used

PParker_at_ins.gte.com (Patrick Parker) wrote in message news:<e6e8dd72.0112121411.7b80d092_at_posting.google.com>...
> I am sure this has an easy solution but,
> Being a bonehead I am having difficulty with this.
> I work with a billing system that has a primary key that ranges
> between 32,000 thru -32,000 roughly, The contractors in their infinite
> wisdom decided to pick numbers randomly from this range to use the
> primary key for the initial Description table and foreign key for a
> number of other tables. I have been trying to write a stored
> procedure to find the null values and then give it the sequential
> number in the series of numbers and return the new set of numbers so
> that they can be used when new products are added to the database.
>
> I have been working on a stored procedure which uses a couple of
> variables set as v_max_value :=32,000, v_min_value, v_count,
> v_new_value
>
> the cursor I am using is select Desc_no /*the Primary key*/ from the
> table
>
> the procedure starts at the v_max_value and a checks if the value is
> not Null
> then v_count = desc_code
> If the value is null and count greater than 0 then v_new_value =
> v_count - 1,
>
> My question is has anyone else had this type of problem? and do you
> have an answer,logic, or stored procedure that you could share with
> me?

Patrick, I am not sure you described your problem correctly. If desc_no is a PK then it cannot be null as a primary key must be by definition not null and unique. Are there entries with a null value for desc_no or are you trying to find the next unused key value in the range?

To find the next avaiable key you use logic like the following (psuedo code):

write a function ck_key_exists(p_keyvalue)   select the_row into v_holder for desc_no = varaiable   return found
  exception
    when no_data_found then return notfound   end

Now starting with you minimun desired key value perform a simple loop that calls the above function until it gets a not found condition. That is your first available key in the range. Continue until you get a hit or your reach the maximum desirec key at which time your range is full and you need to raise an error. Be advised timing issues are possible if several users execute the logic at the same time but do not insert and commit data using the key value immediately upon finding the next key value.

• Mark D Powell --