decode function

I guess, I need help with this one

Let’s say we have an EMP table as below

****************
Name* points
ab * 3200
dc * 100
ef * 20
gh * 0
*****************

and you need to generate a report with columns name and commission ( where commission is decided based on the following conversion table)

******************
point_range * commission
0-100 * 0
101-500 * 50
501-1500* 1000
1501-3000 * 2000
3000+ * 2500
*******************

How would one implement the above requirement, using decode function.
If it cannot be, what is the way out ... use pl/sql code?
Thank you,
nk

Dou you have separate columns for the start and end ranges or they are stored as a string 0-100 etc as you have mentioned. ?

write a small function that returns the commission for the point range. A single if statement would do. If using a procedure, use an OUT variable. I do not think this is a good case for DECODE.

thanks guys !!

To victoria : The conversation table is not in the database itself. It has been given to me for reference purpose. I am expected to use this table to generate a report.

To Kris109:
So, do you suggest that one should write a pl/sql script to handle this (decode would not work in this situation). Right !

Thank you,
nk

select name, points,
case
when points between 0 and 100 then 0
when points between 101 and 500 then 50
when points between 501 and 1500 then 1000
when points between 1501 and 3000 then 2000
when points > 3000 then 2500
end
from emp;

Hi nk,
if ur commission entries are limited u can use the following query.

select name,points,
points*0 RANGE_0_100,
0 RANGE_101_to_500,
0 RANGE_501_to_1500,
0 RANGE_1501_to_3000,
0 RANGE_3000_ABOVE
From EMP
where nvl(points,0) <= 100
union
select name,points,
0 RANGE_0_to_100,
points*50 RANGE_101_to_500,
0 RANGE_501_to_1500,
0 RANGE_1501_to_3000,
0 RANGE_3000_ABOVE
From EMP
where nvl(points,0) > 100 and nvl(points,0) <=500
union
select name,points,
0 RANGE_0_to_100,
0 RANGE_101_to_500,
points*1000 RANGE_501_to_1500,
0 RANGE_1501_to_3000,
0 RANGE_3000_ABOVE
From EMP
where nvl(points,0) > 500 and nvl(points,0) <= 1500
union
select name,points,
0 RANGE_0_to_100,
0 RANGE_101_to_500,
0 RANGE_501_to_1500,
points*2000 RANGE_1501_to_3000,
0 RANGE_3000_ABOVE
From EMP
where nvl(points,0) > 1500 and nvl(points,0) <= 3000
union
select name,points,
0 RANGE_0_to_100,
0 RANGE_101_to_500,
0 RANGE_501_to_1500,
0 RANGE_1501_to_3000,
points*2500 RANGE_3000_ABOVE
From EMP
where nvl(points,0) >= 3000
order by points
/

bye,
with luv,

J.Srinivasan.
Ranipet.

Here is the function:

create or replace function commission(points number)
return number is
commission number;
begin
if points between 0 and 100 then
commission:=0;
elsif points between 101 and 500 then
commission:=points*50;
elsif points between 501 and 1500 then
commission:=points*1000;
elsif points between 1501 and 3000 then
commission:=points*2000;
else
commission:=points*2500;
end if;
return commission;
end;

Here is the query:

SQL> select name, points, commission(points) from emp;

NAME POINTS COMMISSION(POINTS)
---------- ---------- ------------------
ab...............3200..............8000000
dc.................100...................0
ef...................20...................0
gh....................0...................0

[Edited by kris109 on 08-10-2001 at 04:04 PM]

hai nk,

This pl/sql will helpful u to excute automatically, whenever the new commission range
inserted in a table COMM.


Declare
Cursor emp is select name,points From EMP
order by points;
ename varchar2(10);
pts number(10);
Cursor com is select range1,range2,comm From COMM
order by 1;
range1 Number(6);
range2 Number(6);
com_amt Number(5);
amt Number(15,2);
e1name varchar2(10);
Begin
dbms_output.put_line(to_char(null));
dbms_output.put_line(to_char(null));
dbms_output.put_line('---------------------------------------------------------------------------');
dbms_output.put_line('NAME'||' '||'POINTS'||' '||'COMMISSION * POINTS');
dbms_output.put_line('---------------------------------------------------------------------------');
Open emp;
Loop
Fetch emp into ename,pts;
if emp%NOTFOUND then
exit;
else
dbms_output.put_line(to_char(null));
dbms_output.put_line(to_char(null));
Open com;
Loop
fetch com into range1,range2,com_amt;
if com%NOTFOUND then
Exit;
else
Declare
Cursor comamt is select name,points From EMP
where name = ename
and points >= range1
and points <= nvl(range2,100000000000);
amt number(14,2);
e1name varchar2(10);
Begin
Open comamt;
Fetch comamt into e1name,amt;
Close comamt;
dbms_output.put_line(e1name||' '||to_char(amt,'9999999999.99')||' '||to_char(amt*com_amt,'999999999999.99'));
End;
end if;
End loop;
Close com;
end if;
End loop;
dbms_output.put_line('---------------------------------------------------------------------------');
Close emp;
End;
/

with luv,
J.Srinivasan.

If you can't use CASE function and want to stick with DECODE, you can try this one. It will be much quicker than any PL/SQL function or UNIONing repetitive selects...

Code:

---

SELECT
  name,
  DECODE(1,
      SIGN(101 - points), 0,
      SIGN(501 - points), 50,
      SIGN(1501 - points), 1000,
      SIGN(3001 - points), 2000,
      2500
  ) commision
FROM emp
WHERE ....;

---

Very Good, Modic.

Modify Jmodic's code as shown to get the correct commission:

SELECT
name,
(DECODE(1,
SIGN(101 - points), 0,
SIGN(501 - points), 50,
SIGN(1501 - points), 1000,
SIGN(3001 - points), 2000,
2500
))*points commision
FROM emp

Quote:

---

Originally posted by kris109
Modify Jmodic's code as shown to get the correct commission:

SELECT
name,
(DECODE(1,
SIGN(101 - points), 0,
SIGN(501 - points), 50,
SIGN(1501 - points), 1000,
SIGN(3001 - points), 2000,
2500
))*points commision
FROM emp

---

Well, I assume the asterisk (*) character was used as a DELIMITER in the initial post, not as an multiplicator sign. If I am wrong then of course kris109's correction is a valid one.

Jmodic is right. The astrick in the original post was used as a DELIMITER. Moreover 8000000 commission is ridiculous. As usual Jmodic is right on the money.


[Edited by kris109 on 08-14-2001 at 12:41 AM]

Kudos Jurij, another awesome solution.

However, I would back up a step and verify that we actually want to bury this information inside a SQL statement. I fight tooth and nail everytime someone comes up with something like this to put it into the database. It would be a lot more flexible if you created a table for this information:

COMMISSION_T
-------------------
Commission_ID
PointRangeStart
PointRangeEnd
Commission_Amt

Now, not only are your ranges and commissions centralized, data-driven and easily modifiable, but your SQL simplifies to:

SELECT
---E.Name---,
---C.Commission_Amt
------AS Commission
FROM
---Emp------------E,
---Commission_T---C
WHERE
---E.Points---BETWEEN---C.PointRangeStart
------------AND------C.PointRangeEnd---

Food for thought,

- Chris

Quote:

---

Originally posted by jmodic
If you can't use CASE function and want to stick with DECODE, you can try this one. It will be much quicker than any PL/SQL function or UNIONing repetitive selects...

Code:

---

SELECT
  name,
  DECODE(1,
      SIGN(101 - points), 0,
      SIGN(501 - points), 50,
      SIGN(1501 - points), 1000,
      SIGN(3001 - points), 2000,
      2500
  ) commision
FROM emp
WHERE ....;

---

---

Where in the world does he come up with this stuff? I learn something new every day...

Excellent modic,but i need a solution that is whenever a new commission entry comes, i don't want to change my query every time.

Regards,
J.Srinivasan.

Excellent modic,but i need a solution that is whenever a new commission entry comes, i don't want to change my query every time.

Regards,
J.Srinivasan.

If your data changes, you definitly want to take a look at Chris' example.

Quote:

---

Originally posted by marist89

Quote:

---

Originally posted by jmodic
If you can't use CASE function and want to stick with DECODE, you can try this one. It will be much quicker than any PL/SQL function or UNIONing repetitive selects...

Code:

---

SELECT
  name,
  DECODE(1,
      SIGN(101 - points), 0,
      SIGN(501 - points), 50,
      SIGN(1501 - points), 1000,
      SIGN(3001 - points), 2000,
      2500
  ) commision
FROM emp
WHERE ....;

---

---

Where in the world does he come up with this stuff? I learn something new every day...

---

On Metalink, I saw a note about it a couple of days back, let me find the NOTE

/me looking Metalink


Note:110776.1
How to Build an IIF Function in Oracle SQL

How to Build an IIF Function in Oracle SQL:
===========================================

The following question has been asked by Oracle users many times:

"Why do neither ORACLE nor the ANSI standard include a conditional
function such as IIF(A,B,C) from some languages like VB and dBase,
or, (A?B:C) from C in a SQL standard?”

Sometimes programmer code becomes filled with dozens of DECODE function
calls. When you try to optimize the code, it gets unreadable. If you
define a personal standard, this problem can be avoided.

Consider the following methods for solving this problem. For example, you
have the following conditional expression:

IIF( a=d or not (e<5 or f>5) , 'True', 'False' )

Resolve this expression using Oracle SQL possibilities.


Method 1: Using DECODE.
-----------------------

Note: DECODE is not supported in PL/SQL. So, this algorithm IS ONLY for
SQL statements. The main idea is to divide your original expression
into primitives, and then substitute them with a DECODE expression from
a next table.

Conditional
Primitives DECODE Expression Alternative
------------ ------------------------------ ----------------------
A A<=B Decode( Sign(A-B), 1, 0, 1 )
A>B Decode( Sign(A-B), 1, 1, 0 )
A>=B Decode( Sign(A-B), -1, 0, 1 )
A=B Decode( A, B, 1, 0 )
A between B and C Decode( Sign(A-B), -1, 0, Decode(Sign(A-C), 1, 0, 1 ))
A is null Decode(A,null,1,0)
A is not null Decode(A,null,0,1)
A in (B1,B2,...,Bn) Decode(A,B1,1,B2,1,...,Bn,1,0)
nor LogA Decode( LogA, 0, 1, 0 ) (1-Sign(LogA))
LogA and LogB LogA * LogB
LogA or LogB LogA + LogB
LogA xor LogB Decode(Sign(LogA),Sign(LogB),0,1) Mod(Sign(LogA),Sign(LogB),2)


Then you have to cover all but the last DECODE:

DECODE( ,0, ,).

Using the above note, the example is transformed into:


etc etc
etc

[Edited by pando on 08-16-2001 at 06:24 PM]