Matthias Hanft wrote:

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

OK, you caught me - I copy-pasted your SQL. What you may do in some
cases, is to use the IN construct (it may not work with character
columns, I don't quite remember). Add that the case statement doesn't
have to be limited to one column, and you get something like:

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

or, to get your positive/negative bit, I would guess you could do (not
tested)

select
sum (
case when transtype IN ('C','X','F','L') then 1 else - 1 end
*
case currency
when 0 then amount/1.95583
when 1 then amount
else 0.00 /* never happens: don't sum */
end

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

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

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)

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

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)


HTH,
Set