Re: Stored Procedure Exasperation
Under "Stored Procedure Exasperation" I posted my code which returns
unexpected values in a select statement within a "foreach . . . " loop.
Per Graeme's suggestion via Email I have beefed up my procedure to handle
NULL rows better. But it's still not working. For a column named
rate.counter_id my select statement returns these values:
where should actually actual ROWID
counter_id = return returns returned
1 NULL NULL NULL OK
2 2 2 517 OK
3 NULL 2 517 WRONG!
Instead of NULLs, I'm getting the previous row. I don't see anything
wrong with my code and am inclined to think this is an Informix problem.
Any ideas out there?
Ken Miles
Vancouver Division
Hewlett-Packard
kenm@vcd.hp.com
*****************************
Here's the entire procedure:
{drop procedure x;}
create procedure x()
define rate_quantity like rate.quantity;
define v_num_postded int;
define rownum,err_num int;
define v_count_id int;
define rate_count_id like rate.counter_id;
define next_count_id like rate.counter_id;
define v_shift char(3);
define v_shift_date date;
trace on;
let v_shift = "V";
let v_shift_date = "08/05/1993";
foreach
select counter.counter_id
into v_count_id
from counter
begin
on exception in (-1225) set err_num
{ Takes us to the end of the begin .. end block if we encounter
an error trying to assign a NULL to a var which is like rate.counter_id
which doesn't allow nulls. We will then select the next cursor
value for v_count_id.
}
end exception;
let next_count_id = v_count_id;
select rate.rowid, rate.counter_id, rate.quantity
into rownum, rate_count_id, rate_quantity
from rate
where
rate.counter_id = next_count_id and
rate.shift = v_shift and
rate.shift_date = v_shift_date;
trace "next_count_id = " || next_count_id;
trace "rate_count_id = " || rate_count_id;
if rate_count_id != next_count_id then
begin { Take an impossible branch . . . }
raise exception 99,0,"Help! How did I get here?";
end; { If unexpected count_id }
end if;
end { End begin block }
end foreach;
end procedure with listing in "warn.x";
set debug file to "xdebug";
execute procedure x();