martinknappe wrote:

> 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;

Sorry, I had to rewrite the rest to see it clearer...

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

> 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?

You are still fiddling with :id_prior and :asterm_prior which are used
in the outer query. Why don't you write it like this:


begin
pos = 0;
ASTERM_prior = ASTERM_in;
for
select ASTERM
from dicentries
where ASTERM = :ASTERM_in and id >= :id_in
or ASTERM > :ASTERM_in
order by ASTERM ascending
into :ASTERM_temp
do
begin
id_prior = 0;
if (ASTERM_temp = ASTERM_in) then
id_prior = id_in;
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
end
end


--
Milan Babuskov
http://swoes.blogspot.com/
http://www.flamerobin.org