## 14 replies

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

• avt2k7

Member

169 Points

414 Posts

### PL/SQL to calculate commission

`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.

• Lannie

Contributor

3412 Points

1329 Posts

### Re: PL/SQL to calculate commission

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

• avt2k7

Member

169 Points

414 Posts

### Re: PL/SQL to calculate commission

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.

• Lannie

Contributor

3412 Points

1329 Posts

### Re: PL/SQL to calculate commission

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

• avt2k7

Member

169 Points

414 Posts

### Re: PL/SQL to calculate commission

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.

• ryanbesko

Contributor

6988 Points

2154 Posts

### Re: PL/SQL to calculate commission

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.
• avt2k7

Member

169 Points

414 Posts

### Re: PL/SQL to calculate commission

Hi,

Thank for your comment but the error is the same

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

• Lannie

Contributor

3412 Points

1329 Posts

### Re: PL/SQL to calculate commission

Then Expense_cost does not exist in table Sale_Table

• Lannie

Contributor

3412 Points

1329 Posts

### Re: PL/SQL to calculate commission

at SQL>

type

DESCRIBE followed by table name

to get list of COLUMNS

to ensure expense_cost COLUMN exists with underscore and same spelling

• avt2k7

Member

169 Points

414 Posts

### Re: PL/SQL to calculate commission

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.

• Lannie

Contributor

3412 Points

1329 Posts

### Re: PL/SQL to calculate commission

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

/

• avt2k7

Member

169 Points

414 Posts

### Re: PL/SQL to calculate commission

```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.CommissionFROM (   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.

• Lannie

Contributor

3412 Points

1329 Posts

### Re: PL/SQL to calculate commission

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
/
```

• avt2k7

Member

169 Points

414 Posts

### Re: PL/SQL to calculate commission

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.

• Lannie

Contributor

3412 Points

1329 Posts

### Re: PL/SQL to calculate commission

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.