Alan.Davies@... wrote:

>
> select Trans_Date,
> sum(case when Trans_Type=1 then Trans_Amount else 0 end),
> sum(case when Trans_Type=2 then Trans_Amount else 0 end)
> from Trans_Table
> group by Trans_Date

Wow, until now I didn't know that such powerful CASE does
exist at all - since I have a similar application, this
simplifies the "post-processing" in the application a lot.

However, in my special case, I would need something like
"nested" CASEs because I have an additional currency field
- and I can't figure out the correct syntax (if it's possible
at all).

I have a table like this (and I wouldn't like to ALTER that
table because there are too many applications which rely on
this table):

CUSTNO,TRANSTYPE,AMOUNT,CURRENCY
562, C, 12.34 0
562, D, 35.56 1

In addition, TRANSTYPE is not only C or D for credit or debit -
there are also other letters which are credit/debit transactions,
and there are even letters which say "no transaction".

If CURRENCY=0 (Deutsch Marks), the amount must be converted to
Euros (CURRENCY=1) first by dividing the amount by 1.95583.

What I'm looking for is the sum of all transactions of a given
customer, dependent on credit/debit/none and currency. It reads
something like this:

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

("case_below" stands for the result of the second case construct.)

How can I put this into a valid FB SQL statement?

Or would it be easier to pass TRANSTYPE, AMOUNT and CURRENCY to
a UDF which just returns the appropriate result?

Matthias