locked
PL/SQL to calculate commission RRS feed

  • Question

  • User-34860367 posted

    Hi all,

    I got PL/SQL:
    SELECT INVOICE,  GROUP, GROSS_SALE, EXPENSE_COST FROM SaleTable;
    to produce the following result:

    INVOICE GROUP GROSS_SALE EXPENSE_COST PAYMENT_METHOD STATUS ? COMMISSION ? SUM(COMMISSION)
    ----- ---------- ------------- ------------- -------------- --------- 1 A 10,000 3,500 CASH SHIP ? 2 B 4,500 1,000 CREDIT RETURN ? 3 C 900 350 CASH SHIP ? 4 D 1,000 1,525 CREDIT SHIP ? 5 A 450 250 CREDIT RETURN ? 6 B 1,350 350 CHECK SHIP ? 7 C 250 200 CREDIT SHIP ? 8 D 90 0 CREDIT RETURN ?

    etc..

    I would like to add another Commission field in above PL/SQL for calculating the commission base on group column.

    The commission formula:  

    (GROSS_SALE - EXPENSE_COST) * commission percent.

    The commission percent is depending on group column. If group A, then commission percent is 20% (or 0.20); group B: 15%; group C: 10%; group D: 5%. Finally, add another column to calculate the total of commission amount. 

    Any expertise PL/SQL syntax, please help me with the query. Thanks in advance.



     

    Monday, July 20, 2015 1:44 PM

Answers

  • User269602965 posted

    Okay so modify gross sales and expense cost first then  calculate the commission

    Also this will only work if the datatype of sales and cost are NUMBER (not text like CHAR or VARCHAR2)

    You want help, but have not run the DESCRIBE SALE_TABLE so we know col names and datatypes of the data source table,

    so I am in the dark missing important information!

    SELECT 
      b.INVOICE,  
      b.GROUP, 
      b.GROSS_SALE, 
      b.EXPENSE_COST,
      CASE
        WHEN b.GROUP = 'A' THEN (b.GROSS_SALE - b.EXPENSE_COST) * .20
        WHEN b.GROUP = 'B' THEN (b.GROSS_SALE - b.EXPENSE_COST) * .15
        WHEN b.GROUP = 'C' THEN (b.GROSS_SALE - b.EXPENSE_COST) * .10
        WHEN b.GROUP = 'D' THEN (b.GROSS_SALE - b.EXPENSE_COST) * .05
        ELSE 0
      END AS COMMISSION
    FROM
      (
      SELECT
        a.INVOICE,
        a.GROUP,
        CASE 
          WHEN a.PAYMENT_METHOD = 'CREDIT' THEN a.GROSS_SALE
          WHEN a.PAYMENT_METHOD = 'CASH'   THEN a.GROSS_SALE - (a.GROSS_SALE * 0.10) 
          ELSE a.GROSS_SALE - (a.GROSS_SALE * 0.05) 
        END AS AS GROSS_SALE,
        CASE 
          WHEN a.STATUS = 'SHIP'   THEN a.EXPENSE_COST
          WHEN a.STATUS = 'RETURN' THEN a.EXPENSE_COST - (a.EXPENSE_COST * 0.10)
          ELSE 0 
        END AS EXPENSE_COST
      FROM             
        SALE TABLE a
      ) b
    /  
      
    

    Then an additional outer SQL can be done to take values to calculate the sums...
    but you need to define that better to know what we are summing, see last post on the types of summing.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 24, 2015 9:49 AM

All replies

  • User269602965 posted

    Step 1 calculate the commission

    You can use CASE WHEN ELSE END statement

    CASE

      WHEN COLX = 1 then do calc A

      WHEN COLX = 2 then do calc B

      WHEN COLX = 3 then do calc C

      ELSE whatever exceptions you want like NULL

    END AS COMMISSION

    Monday, July 20, 2015 3:33 PM
  • User-34860367 posted

    Hi,

    I tried  CASE WHEN ELSE END statement but displayed  the error on Expense_Cost column. Here is the error:

    ORA-00904: "Expense_Cost ": invalid identifier
    00904. 00000 - "%s: invalid identifier

    -----------------------------------------------------------------------------------------------------------

    Actually, my Gross_sale and Expense_Cost are calculated on the fly. Here is PL/SQL query:  

    SELECT INVOICE, 
           GROUP, 
           (CASE WHEN PAYMENT_METHOD = 'CREDIT' THEN GROSS_SALE
                 WHEN PAYMENT_METHOD = 'CASH' THEN GROSS_SALE - (GROSS_SALE * 0.10) 
                 ELSE GROSS_SALE - (GROSS_SALE * 0.05) END) AS AS Gross_Sale,
           (CASE WHEN STATUS = 'SHIP' THEN EXPENSE_COST
                 WHEN STATUS = 'RETURN' THEN EXPENSE_COST - (EXPENSE_COST * 0.10)
                 ELSE 0 END) AS Expense_Cost,
           (CASE WHEN GROUP = 'A' THEN (Gross_Sale - Expense_Cost)* 0.20
                 WHEN GROUP = 'B' THEN (Gross_Sale - Expense_Cost)* 0.15
                 WHEN GROUP = 'C' THEN (Gross_Sale - Expense_Cost)* 0.10
                 WHEN GROUP = 'D' THEN (Gross_Sale - Expense_Cost)* 0.05
                 ELSE 0.00 END) AS Commission
    
    FROM Sale_Table;

    Please take a look on my above query and let me know what wrong with it? I am thinking about to build a function with GROSS_SALE and EXPENSE_COST parameters. How do I implement it? I really appreciate your time to resolve above error. Thanks. 

    Monday, July 20, 2015 4:14 PM
  • User269602965 posted

    You have to do a nested SQL query

    first inner loop calc the Expense COST

    then in the outer loop you can do your commission stuff using expense

    SELECT

      now calc the commission using expense cost

    FROM

      (

      SELECT

          expense cost calc to get expense cost column

      FROM

         raw data

     )

    /

    Then after you do that, you will need a third outer nesting to do the rollups you want

    Monday, July 20, 2015 11:32 PM
  • User-34860367 posted

    Hi Lannie,

    Thank for your suggestion but I am not sure nested PL/SQL query to build correctly. Can you modify my current query as following:

    SELECT INVOICE, 
           GROUP, 
           (CASE WHEN PAYMENT_METHOD = 'CREDIT' THEN GROSS_SALE
                 WHEN PAYMENT_METHOD = 'CASH' THEN GROSS_SALE - (GROSS_SALE * 0.10) 
                 ELSE GROSS_SALE - (GROSS_SALE * 0.05) END) AS Gross_Sale,
           (CASE WHEN STATUS = 'SHIP' THEN EXPENSE_COST
                 WHEN STATUS = 'RETURN' THEN EXPENSE_COST - (EXPENSE_COST * 0.10)
                 ELSE 0 END) AS Expense_Cost,
           (CASE WHEN GROUP = 'A' THEN (Gross_Sale - Expense_Cost)* 0.20
                 WHEN GROUP = 'B' THEN (Gross_Sale - Expense_Cost)* 0.15
                 WHEN GROUP = 'C' THEN (Gross_Sale - Expense_Cost)* 0.10
                 WHEN GROUP = 'D' THEN (Gross_Sale - Expense_Cost)* 0.05
                 ELSE 0.00 END) AS Commission
    
    FROM Sale_Table;

    My query will run when I took off the chunk of code for commission calculation as following:

    (CASE WHEN GROUP = 'A' THEN (Gross_Sale - Expense_Cost)* 0.20
                 WHEN GROUP = 'B' THEN (Gross_Sale - Expense_Cost)* 0.15
                 WHEN GROUP = 'C' THEN (Gross_Sale - Expense_Cost)* 0.10
                 WHEN GROUP = 'D' THEN (Gross_Sale - Expense_Cost)* 0.05
                 ELSE 0.00 END) AS Commission

    I really appreciate your time to contribute on my query error. Thanks in advance.

    Tuesday, July 21, 2015 10:18 AM
  • User2053451246 posted

    Group is a keyword.  Try enclosing it in brackets, i.e. [GROUP]

    Tuesday, July 21, 2015 10:32 AM
  • User-34860367 posted

    Hi,

    Thank for your comment but the error is the same

    ORA-00904: "Expense_Cost ": invalid identifier
    00904. 00000 - "%s: invalid identifier

    Tuesday, July 21, 2015 11:12 AM
  • User269602965 posted

    Then Expense_cost does not exist in table Sale_Table

    Tuesday, July 21, 2015 1:50 PM
  • User269602965 posted

    at SQL>

    type

    DESCRIBE followed by table name

    to get list of COLUMNS

    to ensure expense_cost COLUMN exists with underscore and same spelling

    Tuesday, July 21, 2015 3:07 PM
  • User-34860367 posted

    Gross_Sale and Expense_Cost are alias column names.  Please ignore any confusion on column names. I changed Gross_Sale to GrossSale and Expense_Cost to ExpenseCost but the same error. What I need to find out whether or not the Oracle syntax that can perform in my current query to commission calculation after calculating GrossSale and ExpenseCost on the fly.   

    Wednesday, July 22, 2015 10:21 AM
  • User269602965 posted

    yes

    possible as stated before with nested SQL

    you do step 1 calculations with alias names

    then do step 2 calculations using the alias names and other cols as required

    then do step 3 outer SQL for any rollups you want by group or dates

    all in one big giant SQL.

    Now i would recommend prefacing col names with table alias like

    below... helps keep nesting cleaner to read and process without errors

    Select

      b.COL1,

      b.COL2,

      b.COL3

    from

      (

      Select

         a.COL1,

          a.COL2,

          a.COL1 * a.COL2 AS COL3

       from table1 a

      )  b

    /

    Wednesday, July 22, 2015 12:31 PM
  • User-34860367 posted
    Just quick come up the query as following:

    SELECT b.INVOICE,
           b.GROUP,
           (CASE WHEN GROUP = 'A' THEN (a.GrossSale - a.ExpenseCost)* 0.20
                 WHEN GROUP = 'B' THEN (a.GrossSale - a.ExpenseCost)* 0.15
                 WHEN GROUP = 'C' THEN (a.GrossSale - a.ExpenseCost)* 0.10
                 WHEN GROUP = 'D' THEN (a.GrossSale - a.ExpenseCost)* 0.05
            ELSE 0.00 END) AS b.Commission

    FROM
    (
    SELECT a.INVOICE,
           a.GROUP,
           (CASE WHEN a.PAYMENT_METHOD = 'CREDIT' THEN a.GROSS_SALE
                 WHEN a.PAYMENT_METHOD = 'CASH' THEN a.GROSS_SALE - (a.GROSS_SALE * 0.10)
              ELSE a.GROSS_SALE - (a.GROSS_SALE * 0.05) END) AS a.GrossSale,
           (CASE WHEN a.STATUS = 'SHIP' THEN a.EXPENSE_COST
                 WHEN a.STATUS = 'RETURN' THEN a.EXPENSE_COST - (a.EXPENSE_COST * 0.10)
              ELSE 0 END) AS a.ExpenseCost FROM Sale_Table a
    ) b ;

    Please take a look my draft above query and please let me know if you can help further. Thanks in advance.

    Thursday, July 23, 2015 3:43 PM
  • User269602965 posted

    This is SQL, not PL/SQL. 

    SELECT is a SQL statement.

    PL/SQL is procedure based language that may include SQL statements.

    I see your sale and cost look like TEXT (VARCHAR2) since Oracle would require a number formatting command to put a comma in the SQL display value,

    (remember I asked for SQL> DESCRIBE SALESTABLE to see the column names and data types used in the table)

    so I added TO_NUMBER to convert what appear to be a TEXT displayed as a number to a real NUMBER datatype for the commission calculation

    The last step is the SUM, which we shall do in an outer SQL statement

    But you did not specify in the last post the type of SUM ing that you need, so specify:

    1. SUM within GROUP, like show the SUM of ALL Group A in each Group A row

    or

    2. Cumulative Sum

        2a.  Cumulative SUM row by row but within each Group

        or

        2b.  Cumulative SUM row by row for all sales ignoring Group

    We shall be using Oracle Analytics to do summing

    SELECT 
      a.INVOICE,  
      a.GROUP, 
      a.GROSS_SALE, 
      a.EXPENSE_COST,
      CASE
        WHEN a.GROUP = 'A' THEN (TO_NUMBER(a.GROSS_SALE,'99999999.99') - TO_NUMBER(a.EXPENSE_COST,'99999999.99')) * .20
        WHEN a.GROUP = 'B' THEN (TO_NUMBER(a.GROSS_SALE,'99999999.99') - TO_NUMBER(a.EXPENSE_COST,'99999999.99')) * .15
        WHEN a.GROUP = 'C' THEN (TO_NUMBER(a.GROSS_SALE,'99999999.99') - TO_NUMBER(a.EXPENSE_COST,'99999999.99')) * .10
        WHEN a.GROUP = 'D' THEN (TO_NUMBER(a.GROSS_SALE,'99999999.99') - TO_NUMBER(a.EXPENSE_COST,'99999999.99')) * .05
        ELSE 0
      END AS COMMISSION
    FROM 
      SALE TABLE a
    /
    

    Thursday, July 23, 2015 8:45 PM
  • User-34860367 posted

    Hi,

    I have to say that we are not the same page. My GROSS_SALE and EXPENSE_COST are numbers. 

    SELECT 
      a.INVOICE,  
      a.GROUP, 
      a.GROSS_SALE, 
      a.EXPENSE_COST,
      CASE
        WHEN a.GROUP = 'A' THEN (TO_NUMBER(a.GROSS_SALE,'99999999.99') - TO_NUMBER(a.EXPENSE_COST,'99999999.99')) * .20
        WHEN a.GROUP = 'B' THEN (TO_NUMBER(a.GROSS_SALE,'99999999.99') - TO_NUMBER(a.EXPENSE_COST,'99999999.99')) * .15
        WHEN a.GROUP = 'C' THEN (TO_NUMBER(a.GROSS_SALE,'99999999.99') - TO_NUMBER(a.EXPENSE_COST,'99999999.99')) * .10
        WHEN a.GROUP = 'D' THEN (TO_NUMBER(a.GROSS_SALE,'99999999.99') - TO_NUMBER(a.EXPENSE_COST,'99999999.99')) * .05
        ELSE 0
      END AS COMMISSION
    FROM 
      SALE TABLE a

    In your above query, you forgot the conditions for GROSS_SALE and EXPENSE_COST.

           (CASE WHEN PAYMENT_METHOD = 'CREDIT' THEN GROSS_SALE
                 WHEN PAYMENT_METHOD = 'CASH' THEN GROSS_SALE - (GROSS_SALE * 0.10) 
                 ELSE GROSS_SALE - (GROSS_SALE * 0.05) END) AS AS Gross_Sale,
           (CASE WHEN STATUS = 'SHIP' THEN EXPENSE_COST
                 WHEN STATUS = 'RETURN' THEN EXPENSE_COST - (EXPENSE_COST * 0.10)
                 ELSE 0 END) AS Expense_Cost,

    If I use CASE WHEN condition for Gross_Sale and Expense_Cost, I will get the above error. You said that I need to use nested query SELECT FROM but your current query looks same my original query. 

    Friday, July 24, 2015 2:30 AM
  • User269602965 posted

    Okay so modify gross sales and expense cost first then  calculate the commission

    Also this will only work if the datatype of sales and cost are NUMBER (not text like CHAR or VARCHAR2)

    You want help, but have not run the DESCRIBE SALE_TABLE so we know col names and datatypes of the data source table,

    so I am in the dark missing important information!

    SELECT 
      b.INVOICE,  
      b.GROUP, 
      b.GROSS_SALE, 
      b.EXPENSE_COST,
      CASE
        WHEN b.GROUP = 'A' THEN (b.GROSS_SALE - b.EXPENSE_COST) * .20
        WHEN b.GROUP = 'B' THEN (b.GROSS_SALE - b.EXPENSE_COST) * .15
        WHEN b.GROUP = 'C' THEN (b.GROSS_SALE - b.EXPENSE_COST) * .10
        WHEN b.GROUP = 'D' THEN (b.GROSS_SALE - b.EXPENSE_COST) * .05
        ELSE 0
      END AS COMMISSION
    FROM
      (
      SELECT
        a.INVOICE,
        a.GROUP,
        CASE 
          WHEN a.PAYMENT_METHOD = 'CREDIT' THEN a.GROSS_SALE
          WHEN a.PAYMENT_METHOD = 'CASH'   THEN a.GROSS_SALE - (a.GROSS_SALE * 0.10) 
          ELSE a.GROSS_SALE - (a.GROSS_SALE * 0.05) 
        END AS AS GROSS_SALE,
        CASE 
          WHEN a.STATUS = 'SHIP'   THEN a.EXPENSE_COST
          WHEN a.STATUS = 'RETURN' THEN a.EXPENSE_COST - (a.EXPENSE_COST * 0.10)
          ELSE 0 
        END AS EXPENSE_COST
      FROM             
        SALE TABLE a
      ) b
    /  
      
    

    Then an additional outer SQL can be done to take values to calculate the sums...
    but you need to define that better to know what we are summing, see last post on the types of summing.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 24, 2015 9:49 AM