Re: Cheryl's Question
This is in response to cheryl@rrad05.army.mil with getting the next
number.
Try this:
#------------------------------------------------------------------------------
function get_next_number()
define new_number integer
let new_number = 0
while new_number = 0
# Only One Row in table which holds the next number in sequence
declare new_num_curs cursor with hold for
select number from number_table
for update
if sqlca.sqlerrd[2] = -107 then
continue while
end if
open new_num_curs
if sqlca.sqlerrd[2] = -107 then
continue while
end if
fetch new_num_curs into new_number
if sqlca.sqlerrd[2] = -107 then
close new_num_curs
free user_rec_curs
let new_number = 0
continue while
end if
if status = notfound then
let new_number = 1
insert into number_table values ( new_number )
if sqlca.sqlerrd[2] = -100 then
let new_number = 0
continue while
end if
end if
# If you want to reset back to 1, do it here
update number_table set number = number + 1
close new_num_curs
free new_num_curs
end while
return new_number
end function
#------------------------------------------------------------------------------
The table should look like this:
create table number_table ( number integer );
create unique index nt1 on number_table( number );
#------------------------------------------------------------------------------
Hope this helps,
Robert Minter |Data Systems Support| \\\\\\_///
Programmer, Software Development | Orange, CA | ( _ _ )
internet: rob@dssmktg.com | Tel: 714.771.0454 | (| ^ |)
bangpath: uunet.uu.net!dssmktg!rob| Fax: 714.771.3028 | \\`-'/
I used to not know, now I know I don't know. \\_/