decode function

[nk]

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

[Victoria]

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

[kris109]

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.

[nk]

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

[kmesser]

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;

[sriesha]

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.

[kris109]

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]

[sriesha]

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.

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

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

[tamilselvan]

Very Good, Modic.