 14 replies

Last post Jul 24, 2015 09:49 AM by Lannie

• PL/SQL to calculate commission

Jul 20, 2015 01:44 PM|avt2k7|LINK

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.

• Re: PL/SQL to calculate commission

Jul 20, 2015 03:33 PM|Lannie|LINK

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

• Re: PL/SQL to calculate commission

Jul 20, 2015 04:14 PM|avt2k7|LINK

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.

• Re: PL/SQL to calculate commission

Jul 20, 2015 11:32 PM|Lannie|LINK

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

• Re: PL/SQL to calculate commission

Jul 21, 2015 10:18 AM|avt2k7|LINK

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.

• Re: PL/SQL to calculate commission

Jul 21, 2015 10:32 AM|ryanbesko|LINK

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

Mark all posts that give the desired result the answer. If you only mark the last that gave you clarification because you misread an earlier post others will be confused. Some of us are here to help others and our point to post ratio matters.
• Re: PL/SQL to calculate commission

Jul 21, 2015 11:12 AM|avt2k7|LINK

Hi,

Thank for your comment but the error is the same

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

• Re: PL/SQL to calculate commission

Jul 21, 2015 01:50 PM|Lannie|LINK

Then Expense_cost does not exist in table Sale_Table

• Re: PL/SQL to calculate commission

Jul 21, 2015 03:07 PM|Lannie|LINK

at SQL>

type

DESCRIBE followed by table name

to get list of COLUMNS

to ensure expense_cost COLUMN exists with underscore and same spelling

• Re: PL/SQL to calculate commission

Jul 22, 2015 10:21 AM|avt2k7|LINK

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.

• Re: PL/SQL to calculate commission

Jul 22, 2015 12:31 PM|Lannie|LINK

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

/

• Re: PL/SQL to calculate commission

Jul 23, 2015 03:43 PM|avt2k7|LINK

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.

• Re: PL/SQL to calculate commission

Jul 23, 2015 08:45 PM|Lannie|LINK

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
/

• Re: PL/SQL to calculate commission

Jul 24, 2015 02:30 AM|avt2k7|LINK

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.

• Re: PL/SQL to calculate commission

Jul 24, 2015 09:49 AM|Lannie|LINK

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.