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?

Totti wrote:
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Totti wrote:
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Totti wrote:
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Totti wrote:
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thank you Mr. D.A. Morgan

i am sorry, i didnt make my self clear, it was my fault , the way i
wrote the procedure; i meant to say :
i.e.
if ('base' and 'east') --> 1.5%,
if ('base' and 'west') --> 2.2%,
if ('base' and 'north')--> 0.95% .....
else 0 end.
and so on so forth,
than add % s under 'base' to get the final amount paid for carrying
it, since the fee is a percentage of the sales.
the reason i want to use case is because i thought it might do my job
since i wanted to do the math of all these percentages and i thought
that sum of case would be a good approach.

Thank you Mr. D.A. Morgan

i am sorry, i didnt make my self clear, it was my fault , the way i
wrote the procedure; i meant to say :
i.e.
if ('base' and 'east') --> 1.5%,
if ('base' and 'west') --> 2.2%,
if ('base' and 'north')--> 0.95% .....
else 0 end.
and so on so forth,
than add % s under 'base' to get the final amount paid for carrying
it, since the fee is a percentage of the sales.
the reason i want to use case is because i thought it might do my job
since i wanted to do the math of all these percentages and i thought
that sum of case would be a good approach.

Thank you Mr. D.A. Morgan

i am sorry, i didnt make my self clear, it was my fault , the way i
wrote the procedure; i meant to say :
i.e.
if ('base' and 'east') --> 1.5%,
if ('base' and 'west') --> 2.2%,
if ('base' and 'north')--> 0.95% .....
else 0 end.
and so on so forth,
than add % s under 'base' to get the final amount paid for carrying
it, since the fee is a percentage of the sales.
the reason i want to use case is because i thought it might do my job
since i wanted to do the math of all these percentages and i thought
that sum of case would be a good approach.

Thank you Mr. D.A. Morgan

i am sorry, i didnt make my self clear, it was my fault , the way i
wrote the procedure; i meant to say :
i.e.
if ('base' and 'east') --> 1.5%,
if ('base' and 'west') --> 2.2%,
if ('base' and 'north')--> 0.95% .....
else 0 end.
and so on so forth,
than add % s under 'base' to get the final amount paid for carrying
it, since the fee is a percentage of the sales.
the reason i want to use case is because i thought it might do my job
since i wanted to do the math of all these percentages and i thought
that sum of case would be a good approach.

On Jan 6, 10:06*am, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
Take a look at the documentation for the DECODE statement, as it might
be exactly what you want. For example, consider the following nested
DECODE:
SUM(DECODE(PROD_1.CATEGORY,'Base',
DECODE(CUST_1.REGION,
'East',SALPMT_1.AMOUNT*1.015,
'West',SALPMT_1.AMOUNT*1.022,
'North',SALPMT_1.AMOUNT*1.0095,
0),
0))

The above is read like this:
If PROD_1.CATEGORY = 'Base' Then
If CUST_1.REGION = 'East' Then
Return SALPMT_1.AMOUNT*1.015 (101.5% of SALPMT_1.AMOUNT)
Else
If CUST_1.REGION = 'West' Then
Return SALPMT_1.AMOUNT*1.022 (102.2% of SALPMT_1.AMOUNT)
Else
If CUST_1.REGION = 'North' Then
Return SALPMT_1.AMOUNT*1.0095 (100.95% of SALPMT_1.AMOUNT)
Else
Return 0
End If
End if
End If
Else
Return 0
End If

If personally prefer to use DECODE rather than CASE, as it is
typically less typing, and easy to understand if you keep track of the
() and commas.

It looks like you are trying to use a difficult method to learn SQL...
picking a complicated problem, and trying to find a SQL solution to
the problem that requires nested DECODEs, multiple tables, etc. I
would suggest starting with much easier examples by working through a
book that covers basic SQL, rather than jumping attempting solutions
like one might find in "SQL for Smarties". The book should be written
to cover the Oracle specific SQL syntax, rather than a generic one
that is intended for Microsoft Access and SQL Server developers. The
Oracle documentation includes a SQL reference that might be a good
starting point.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.