Stored Procedure Exasperation
Why is it doing this to me?
I am running OnLine 5.00, HPUX 8.07 on an HP9000/750.
Within a stored procedure I have the following code:
> foreach
> select counter_id
> into v_count_id
> from counter>
> begin
>
> select rowid, counter_id, quantity
> into rownum, table_count_id, t_old_quantity
> from rate_summary
> where
> rate_summary.counter_id = v_count_id and
> rate_summary.shift = v_shift and
> rate_summary.shift_date = v_shift_date;>
> trace "v_count_id = " || v_count_id;
> trace "table_count_id = " || table_count_id;
>
> if table_count_id != v_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;
There are around 165 values in the counter table but we only get as far as
the third "counter_id" in the rate_summary table and then the impossible
branch executes. The select on rate_summary actually returns values
which were correct for the second pass through, not the third.
I had suspected a corrupted table but "tbcheck cI" (among others) on this
table shows no problems. Maybe I should rebuild it anyway?
The table "counter" is a local table but rate_summary is a synonym to a table in
another database but still within the same OnLine environment.
If there is a bug in the above I have been looking at it for too long to
see it.
Ken Miles
Vancouver Division
Hewlett-Packard
Kenm@vcd.hp.com
**************************************
For the REALLY curious, here is what went into the trace file:
trace on
let v_shift = V
let v_shift_date = 08/05/1993
let v_starttime = 1993-08-05 06:45:00
let v_endtime = 1993-08-05 15:00:00
expression:(- v_endtime, v_starttime)
evaluates to 0 08:15:00
let v_duration = 0 08:15:00
let old_shift = V
let old_date = 08/05/1993
start select cursor.
select counter_id
from counterselect cursor iteration.
select cursor returns 1
expression:
(select rowid, counter_id, quantity
from rate_summary
where (and (and (= rate_summary.counter_id, v_count_id), (= rate_summary.shift, v_shift)), (= rate_summary.shift_date, v_shift_date)))
evaluates to NULL. ; NULL. ; NULL. ;
let rownum = NULL.
let table_count_id = NULL.
let t_old_quantity = NULL.
expression:(|| "v_count_id = ", v_count_id)
evaluates to v_count_id = 1
trace expression :v_count_id = 1
expression:(|| "table_count_id = ", table_count_id)
evaluates to NULL.
trace expression :NULL.
expression:(<> table_count_id, v_count_id)
evaluates to NULL.
let old_shift = V
let old_date = 08/05/1993
select cursor iteration.
select cursor returns 2
expression:
(select rowid, counter_id, quantity
from rate_summary
where (and (and (= rate_summary.counter_id, v_count_id), (= rate_summary.shift, v_shift)), (= rate_summary.shift_date, v_shift_date)))
evaluates to 3 ; 2 ; 160789 ;
let rownum = 160789
let table_count_id = 2
let t_old_quantity = 3
expression:(|| "v_count_id = ", v_count_id)
evaluates to v_count_id = 2
trace expression :v_count_id = 2
expression:(|| "table_count_id = ", table_count_id)
evaluates to table_count_id = 2
trace expression :table_count_id = 2
expression:(<> table_count_id, v_count_id)
evaluates to 0
let old_shift = V
let old_date = 08/05/1993
select cursor iteration.
select cursor returns 3
expression:
(select rowid, counter_id, quantity
from rate_summary
where (and (and (= rate_summary.counter_id, v_count_id), (= rate_summary.shift, v_shift)), (= rate_summary.shift_date, v_shift_date)))
evaluates to 3 ; 2 ; 160789 ;
let rownum = 160789
let table_count_id = 2
let t_old_quantity = 3
expression:(|| "v_count_id = ", v_count_id)
evaluates to v_count_id = 3
trace expression :v_count_id = 3
expression:(|| "table_count_id = ", table_count_id)
evaluates to table_count_id = 2
trace expression :table_count_id = 2
expression:(<> table_count_id, v_count_id)
evaluates to 1
raise exception :99, 0, "Help! How did I get here?"
exception : looking for handler
SQL error = 99 ISAM error = 0 error string = = "Help! How did I get here?"exception : no appropriate handler