Matthias Hanft wrote:

> 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).

Oh, sorry, I actually didn't know this.

>> 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!

WHAT??? That doesn't make sense. Are you sure you did:

select
b.custno,
sum(
case b.cleared
when 0 then
case b.currency
when 0 then cast(b.amount/1.95583 as numeric(9,2))
when 1 then b.amount
else NULL
end
* t.factor
else NULL
end
) as balance,
sum(
case
when b.transtype='R' and b.cleared=0 then 1 /* reminder */
else NULL
end
) as reminders,
max(
case t.invpaynone /* invoice or payment or none */
when 'I' then b.transdate /* invoice */
else NULL
end
) as last_invoice,
max(
case t.invpaynone
when 'P' then b.transdate /* payment */
else NULL
end
) as last_payment,
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
from billing b join transtypes t on b.transtype=t.transtype
where company=?
and EXISTS(select * from billing b3
where b3.custno = b.custno
and b3.cleared = 0)
group by b.custno
order by b.custno;

What is the plan reported? 11 minutes and 37 seconds sounds a bit long
and I'm surprised that it takes longer than the HAVING clause (assuming
you wouldn't have complained if the HAVING clause took 15 minutes).

> 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?

Yes, I'm afraid you have to repeat the entire statement (though I still
don't think you have to use HAVING for your statement).

> [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...

When I try something similar (GROUP BY, then SUM with case and NOT
EXISTS), things work OK in IB_SQL. I added it to the longer statement
above for you to try once more.

Set