Svein Erling Tysvær wrote:

>> cast(amount/(1.95583-(currency*0.95583)) as numeric(9,2))

Hey, that's cute! :-) But I think I'll use the WHEN construct
anyway - just in case there will be a CURRENCY=2,3... one day
(you never know).

> Nested case statements are allowed:
>
> select
> sum (
> case transtype
> when 'C','X','F','L':
> case currency
> when 0 then amount/1.95583
> when 1 then amount
> else 0.00 /* never happens: don't sum */
> end
> when 'D','A','M','Z':
> case currency
> when 0 then -amount/1.95583
> when 1 then -amount
> else 0.00 /* never happens: don't sum */
> end
> else 0.00 /* don't sum at all */
> end
> ) as customersum
> from transactiontable
> where custno=?

OK, but putting it this way you have to specify the 1.95583
division twice. Admittedly, the sign flag is already included
in the division.

Does a WHEN comma-seperated list really work? I just put it
down that way for clarity, but I'm afraid in real life, it
should read when transtype='C' or transtype='X' or...?!

Meanwhile, I have created 3 additional columns (number of
reminders, date of last invoice, date of last payment) and
extended the query from one customer to all customers of
a company, showing only the customers with a non-zero
balance (this means: who have at least one row where the
"cleared" flag is still zero). Now it looks like this:

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
from billing b join transtypes t on b.transtype=t.transtype
where company=?
group by b.custno
having
sum(
case b.cleared
when 0 then 1
else NULL
end
) is not null
order by b.custno;

This works great - and now, my last wish is a column "number of
reminders since last payment". So, the SUM of transtype='R' had
to be "resetted" when a transtype='P' is encountered. Is this
possible within an SQL statement at all?

Matthias