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.

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
Current memory = 1.217.384
Max memory = 1.450.008
Memory buffers = 2.048
Reads from disk to cache = 1.632
Writes from cache to disk = 0
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.)

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
Current memory = 1.106.184
Max memory = 1.450.008
Memory buffers = 2.048
Reads from disk to cache = 0
Writes from cache to disk = 0
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).

But as far as the subSELECT for the "current_reminders" is
concerned, I have no idea either...

Matthias