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

On Jan 6, 10:06 am, Totti <saliba.toufic.geo..._at_gmail.com> wrote:
> 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.

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

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

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. Received on Sun Jan 06 2008 - 11:23:59 CST