Re: how can i put multiple statmwnts in sum(case...
Date: Sun, 06 Jan 2008 02:04:55 -0800
Message-ID: <1199613868.6883@bubbleator.drizzle.com>
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.orgReceived on Sun Jan 06 2008 - 04:04:55 CST