hi

i know this is getting annoying but after i thought i'd got my sp
right and tinkered a little with it, after a closer look at the result
set i came to realise it's still not correct; i'll start explaining
from the top so that everyone can follow:

the procedure is supposed to do the following (semantically):

input:

id_in (bigint)
asterm_in (varchar)

output:

id_out (bigint)
pos (integer)

begin procedure
pos = 0;
for select first 20 id from dicentries where (asterm = :asterm_in and
id >= :id_in) or (asterm > :asterm_in) order by asterm ascending, id
ascending into :id_out do
begin
suspend;
pos = pos + 1;
end
end procedure

now, my fields are too big for a joint index on asterm ascending, id
ascending to be created so i had to rewrite the procedure in a more
complicated way. note: the procedure as follows gives the correct
results when executed within the ibexpert debugger (!) but not when fb
1.5 is in charge:

CREATE PROCEDURE NEXT_20_AB_ASTERM (
asterm_in varchar(240),
id_in bigint)
returns (
id_out bigint,
pos integer)
as
declare variable id_prior bigint;
declare variable asterm_temp varchar(240) character set unicode_fss;
declare variable asterm_prior varchar(240) character set unicode_fss;
begin
pos = 0;
ASTERM_prior = ASTERM_in;
id_prior = id_in;
for select ASTERM from dicentries where ((ASTERM = :ASTERM_prior and
id >= :id_prior) or (ASTERM > :ASTERM_prior)) order by ASTERM
ascending into :ASTERM_temp do
begin
if (ASTERM_temp > ASTERM_prior) then
id_prior = 0;
for select id from dicentries where ASTERM = :ASTERM_temp and id

>= :id_prior order by id ascending into :id_out do

begin
suspend;
pos = pos + 1;
if (pos = 20) then
exit;
end
id_prior = id_out + 1;
asterm_prior = asterm_temp;
end
end


with my sample database, when i execute the procedure as follows
WITHIN THE IBEXPERT debugger:

select * from next_20_ab_asterm('Lackreiniger, m', 34)

i get the following (correct) result:

id_out

34
167 //note that from this record on, asterm > asterm_in, so id_out may
117 //be smaller than id_in for these records!
59
91
123
143
141
185
219
206
207
1
223
213
177
175
130
90
110

-> as you can see, there are a couple records where id_out < id_in;
that is because (asterm > asterm_in) has precedence over (id >= id_in)

now, when i execute the same query outside the debugger, i get the
following (wrong) result:

34
167
117
59
143
141
185
219
206
207
177
130
90
110
222
82
79
211
212
210

as you can see here, all those records of the correct result set have
been left out, where id_out < id_in, and its next successor where
id_out > id_in was taken into the result set instead
alas, i have tried to fix this in several different ways but it didnt
work..would anyone please have a closer look at it again?


thanx

martin