--- In firebird-support@yahoogroups.com, Matthias Hanft wrote:

> I wrote:
>
> > How can I put this into a valid FB SQL statement?
>
> Meanwhile, I remembered that there is a table "TRANSTYPES" like
> this:
>
> TRANSTYPE,FACTOR
> C 1
> D -1
> X 0
> [and so on]
>
> from where I can get the information if a certain transaction is
> credit or debit (or none).
>
> Using that table, I have now a functionning SQL statement like this:
>
> select
> sum(
> case currency
> when 0 then cast(amount/1.95583 as numeric(9,2))
> when 1 then amount
> else NULL
> end
> *
> factor
> )
> as balance
> from transactiontable
> left join transtypes
> on transactiontable.transtype=transtypes.transtype
> where custno=?
>
> Does this go any better?

If there is always a transtype, you want an [INNER] JOIN, not a LEFT
[OUTER] JOIN (be careful with LEFT JOINS, they should be used when
simply JOIN doesn't produce an adequate result).

Moreover, your statement can be "simplified" (in double quotes because
it is more difficult to understand) to

> select
> sum(
> cast(amount/(1.95583-(currency*0.95583)) as numeric(9,2))
> *
> factor
> )
> as balance
> from transactiontable
> join transtypes
> on transactiontable.transtype=transtypes.transtype
> where custno=?

> (Nevertheless, I still wonder if I can work with "intermediate" CASE
> results as asked in my last message.)

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

Set