Svein Erling Tysvaer wrote:

> case
> when transtype IN ('C','X','F','L') and currency = 0 then...

IN seems to work with character constants as above. This makes
the code more readable.

> Are you sure this is what your code is doing? I'd say that your code
> requires all rows for the customer to have the cleared flag 0. If the
> cleared flag is not 0, then you use NULL (not 0), and summing anything
> containing NULL has to yield NULL (if there is one unknown, you can't
> know the sum).

This sounds perfectly logical, but according to my results and Helen's
book, "rows containing NULL in the targeted column are ignored for the
aggregation" (p.475).

So, the SUM is only NULL if _all_ rows have NULL in this column.

> The HAVING clause can be useful, but only when you want something
> working on your result set. From what you write, I'd rather use an
> EXISTS(subselect) in the WHERE clause, if you intend it to be like you
> wrote above:
>
> EXISTS(select * from billing b2
> where b2.custno = b.custno
> and b2.cleared = 0)

Doesn't work - in two respects:
- shows _all_ customers in the result set; just the BALANCE is
NULL if there are only b2.cleared rows;
- took 11 minutes and 37 seconds, even with indexed reads only!

So I'll keep to

having
sum(
case b.cleared
when 0 then 1
else NULL
end
) is not null

Apropos "working on the result set": I discovered that the following
doesn't seem to work:

select SUM ( ...very long and complicated expression ... ) as SOMETHING
having SOMETHING>100;

SOMETHING is apparently not known for the use in HAVING. Does one
have to repeat the complete SUM expression for that?

[Number of reminders after last payment]

> Well, I guess you could do this two ways, both of them requiring one or
> more subselects. The one you probably want is this:
>
> SUM(
> case
> when 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)

Hmmm... get some error -104 which IBExpert falsely translates into
"user name required". Have to test this with ISQL to get the real
error message. But it points me to the right direction: Just count
the reminders after MAX(payment_date) or something like that...

Matthias