Matthias Hanft wrote:

> Svein Erling Tysvaer wrote:
>
>> WHAT??? That doesn't make sense. Are you sure you did:
> [...]
>
> Whenever I insert the following section for the "current_
> reminders" result column:
>
>> SUM(
>> case
>> when b.transtype = 'R'
>> and NOT EXISTS(select * from billing b2
>> where b2.custno = b.custno
>> and b2.transtype = 'P'
>> and b2.transdate >= b.transdate)
>> then 1 else 0 end) as current_reminders
>
> I get this error message:
>
> Dynamic SQL Error
> -SQL error code = -104
> -Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
>
> If I remove that section, the query runs fine again.

Well, I only know that I don't get any such error with a similar query
and Firebird 1.5.3.

> Concerning the selection which users to show:
>
> Your version:
>
>> and EXISTS(select * from billing b3
>> where b3.custno = b.custno
>> and b3.cleared = 0)
>
> Plan:
> PLAN (B3 INDEX (ICLEARED))
> PLAN SORT (JOIN (B INDEX (RDB$FOREIGN15),T INDEX (RDB$PRIMARY1)))
> Adapted plan:
> PLAN (B3 INDEX (ICLEARED)) PLAN SORT (JOIN (B INDEX (INTEG_47),T INDEX (INTEG_2)))
> ------ Performance info ------
> Prepare time = 31ms
> Execute time = 11m 55s 391ms
> Avg fetch time = 21.040,91 ms
> Fetches from cache = 654.825.498
>
> And the result is even wrong: _All_ customers are shown, even those
> where all rows are already cleared (=have non-zero CLEARED column).
> (The BALANCE is NULL then.)

Yes, I see, sorry. It is not all customers that are shown, but I forgot
that the same custno may exist in several companies. Change to

and EXISTS(select * from billing b3
where b3.custno = b.custno
and b3.company = b.company
and b3.cleared+0 = 0)

The reason for the slowness may also be that the ICLEARED index is not
very selective and that my subselect didn't use any other indexes for
that table. Hopefully, this is due to me not specifying the company
above and that you have an index on (company, custno) or similar (I
assumed - probably incorrectly - that you had an index on custno by
itself). Also, due to the extreme slowness I added +0, if your ICLEARED
index does contain (cleared, company, custno) or similar, you may want
to remove this +0.

> My version:
>
>>> having
>>> sum(
>>> case b.cleared
>>> when 0 then 1
>>> else NULL
>>> end
>>> ) is not null
>
> Plan:
> PLAN SORT (JOIN (B INDEX (RDB$FOREIGN15),T INDEX (RDB$PRIMARY1)))
> Adapted plan:
> PLAN SORT (JOIN (B INDEX (INTEG_47),T INDEX (INTEG_2)))
> ------ Performance info ------
> Prepare time = 15ms
> Execute time = 1s 125ms
> Avg fetch time = 33,09 ms
> Fetches from cache = 345.305
>
> and the "customer selection result" is correct: Only those
> customers are shown which have at least one CLEARED=0 row.
>
> I think the main difference between your and my version is:
>
> - with your WHERE, only the single "taken-in-account" rows
> for the various SUMs are selected, but not the (GROUPed)
> CUSTNOs which are finally shown;
>
> - with my HAVING, I can make a selection among the "GROUP BY
> CUSTNO" (which of them I want to show finally).

These may well be factors, but the difference should be a lot less (I
don't know which will be the fastest, but 1.125 seconds sounds good, so
maybe HAVING beats EXISTS).

Set