--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> Hi again!
>
> Your procedure is similar to what I suggested, but probably a bit
> slower. At first glance, I do not see how it could return NULL, but I
> haven't looked closely since this may be gone when you clean up your
> procedure and if not, then it could possibly be easily circumvented by
> adding WHERE IDOUT IS NOT NULL to your calling query.

well, that would prevent entries w/o an idout value from appearing in
the result set, but it's not the solution to the problem; it's just a
cover-up which effectively hides data away from the user when he is
really requesting it..as a matter of fact, for a simple test, i
changed my procedure in such a way that it also returned the proper
value for asterm of the result set entries..what i did then was copy
asterm of those entries that had null in their id field and queried
the following:

select id from dicentries where asterm = copiedValue

and it did give me an id so this shows that the id really exists..i
don't know what's going on here..im nearly so far as to claim this is
a bug..

> You seem to prefix variables with a colon everywhere.

afaik that's absolutely necessary

> Moreover, SET TERM is part of the
> stored procedure language, so I would try to avoid having TERM as
> the name of a parameter.

ok, i tried with a different identifier; didn't help either

> You combine SELECT FIRST with counting manually,
> and that is just wasting time and resources.

i know, i tried "select min(id)..." before but then tried with "first"
just to see if that was the problem..it really kept me busy for a good
while yesterday :-(

> And finally, is ASTERM at
> all indexed

yes it is; in the first post of this thread theres a link to the sql
script for creating the database where you can look everything up

i've nearly given up..what ive done now is some kind of a
workaround...whereas before the select procedure my client user would
invoke did something like:

select first 20 id from dicentries
where (asterm = :asterm and id >= :id) or (asterm > :asterm)
order by asterm ascending, id ascending

i now changed the procedure so that it be more selective:

select first 20 id from dicentries
where ((asterm = :asterm and id >= :id) or (asterm > :asterm)) and
(asterm <= :upperbound)
order by asterm ascending, id ascending

so now it's in the user's hand to make response time shorter by simply
"cutting off" the end of the table, which he wouldn't have seen in his
result set anyway...not the nices of solutions but better than keep
the user waiting for 7 seconds on every page scroll...

thanx anyway,

martin