Matthias Hanft wrote:

> Ok, I didn't want to blame FB for errors, so I should have rather typed
> "the results weren't quite what I had expected" :-)

A quick look revealed what is wrong (sorry I didn't notice before):

> sum(
> case
> when (b.transtype = 'R') /* reminder */
> and
> (b.transdate > (select transdate from vlastpay v
> where company=2 and v.custno = b.custno))
> then 1
> else 0
> end
> ) as current_reminders

There is no alias to company, so it could be either v.company or
b.company. I would expect Firebird to give an error for such ambiguous
queries, but maybe I'm wrong or you not using Firebird 1.5.3. Though I
would expect the subselect to cause something like 'multiple rows in
singleton select' if the optimizer used b.company rather than v.company
and two companies could have the same custno, so it is still surprising
that you get the wrong result.

Anyway, change to 'where v.company=2' and you ought to get correct
results. It is at least worth checking the plan, if it looks horrendous,
it isn't necessary to execute it (I don't want to cause you to wait
several hours). Choosing the wrong index in a subselect can destroy all
performance, and change the execution time from seconds to hours (I think).

Also, I should say that most 'Firebird support gurus' choose to use a
(comparatively simple) selectable stored procedure when calculations get
complicated, rather than use a very complex select (that is at least my
impression from this list). Sometimes, it can also improve performance.
Though I don't know whether your select is considered complicated or not
or whether using an SP could improve the performance.

Set