mikcaau wrote:

> Yet another book keeping routine
> this fails
> select /*mas.DIRECTION,*/
> mas.CUSTID,
> Cust.CUST_NAME as Cust_Sup,
>
> count(mas.ID),
> case
> when mas.DIRECTION = 'CREDIT' then sum( mas.ENTRY_TOTAL)
> else 0
> end as Credit,
>
> case
> when mas.DIRECTION = 'DEBIT' then Sum( mas.ENTRY_TOTAL)
> else 0
> end as Debit
>
> from TRANS89 mas
> left join MACT_CUST_SUP_LIST Cust on Cust.CUSTID = mas.CUSTID
>
> where Cust.CUSTID in ('7', '8', '9')
>
> group by 1, 2/*, 3*/
>
> order by 1
>
> But if I remove the comments it runs ok but I get money direction
> which I am trying to avoid.
> FB 1.5 win xp server and client
>
> Am I stuck with getting Direction?

No Mick,

you don't have to get direction, just change your query slightly. Though
before I show that solution to you, you are aware that since you are
referring to CUST in the WHERE clause, you are in reality contradicting
the LEFT part of your JOIN? If you really want it to be a LEFT JOIN,
move that clause as shown in my suggestion below, if you just want a
normal [inner] JOIN, simply remove LEFT. Only if the optimizer
originally chose a bad plan and you decided to use 'LEFT' to help the
optimizer choose a better plan, I'd say that your current solution is
OK. In general, I use JOIN wherever I can, and LEFT JOIN when inner JOIN
do not get the desired result. I find that a good rule to adhere to in
order to let the optimizer do its work without too much restrictions.

The solution to the problem you asked about, is to 'sum the case' rather
than 'case the sum' ;o)

select mas.CUSTID,
Cust.CUST_NAME as Cust_Sup,
count(mas.ID),
sum(
case
when mas.DIRECTION = 'CREDIT' then mas.ENTRY_TOTAL
else 0
end) as Credit,
sum(
case
when mas.DIRECTION = 'DEBIT' then mas.ENTRY_TOTAL
else 0
end) as Debit
from TRANS89 mas
left join MACT_CUST_SUP_LIST Cust
on Cust.CUSTID = mas.CUSTID
and Cust.CUSTID in ('7', '8', '9')
group by 1, 2

HTH,
Set