Re: how can i put multiple statmwnts in sum(case...

Totti wrote:
> hi all, how ca put multiple statmwnts in sum(case statment?
> like if i have 4 regions, and 1 product, if for each region the
> transport fee is different, in a sum case how can that be done, is
> there any " nested if statment". i tried this and of course this is
> not the correct result:
>
> SUM(CASE WHEN prod_1.category = 'Base' AND CUST_1.REGION = 'East'
> then(salpmt_1.amount*0.150 + salpmt_1.amount) else 0 end)AS"Base",
> SUM(CASE WHEN prod_1.category = 'Base' AND CUST_1.REGION = 'West'
> then(salpmt_1.amount*0.170 + salpmt_1.amount) else 0 end)AS"Base",
>
> but this leads to 2 Base the one near the other, while what i need
> is :
>
> if base and east = x, if base and west = y, if base and north =
> z ..... else 0 end.
>
> how could that be done please?

Is there some reason you need to use CASE?

IF base = x AND east = x THEN

   ...
ELSIF base = y and east = y THEN

   ...

Now take that and code it using CASE.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org