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.

[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

[jmodic]

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.

[kris109]

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]

[chrisrlong]

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

[marist89]

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

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

[sriesha]

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.

[sriesha]

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.

[marist89]

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

[pando]

Originally posted by marist89
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...

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<b and c>=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<B Decode( Sign(A-B), -1, 1, 0 )
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( <condition>,0, <false>,<true>).

Using the above note, the example is transformed into:


etc etc
etc

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

[jmodic]

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

Note:110776.1

...SNIP....
A between B and C Decode( Sign(A-B), -1, 0, Decode(Sign(A-C), 1, 0, 1 ))
...SNIP....


I personaly hate using nested DECODEs. The fact is, you can almost allways avoid nesting DECODES with a slightly different technique. Isn't the following representation of "A between B and C" more elegant than the one above?


DECODE(1, SIGN( SIGN(A-B) + SIGN(C-A)), 1, 0)


(In result 1 means TRUE and 0 means FALSE)

[chrisrlong]

Originally posted by jmodic

DECODE(1, SIGN( SIGN(A-B) + SIGN(C-A)), 1, 0)



This stuff looks a whole lot like the greek in Joe Celko's Advance SQL book. I'll say now what I said then... Thank God for the CASE statement!!! ;)

- Chris

[jmodic]

Originally posted by chrisrlong
Originally posted by jmodic

DECODE(1, SIGN( SIGN(A-B) + SIGN(C-A)), 1, 0)



This stuff looks a whole lot like the greek in Joe Celko's Advance SQL book. I'll say now what I said then... Thank God for the CASE statement!!! ;)

- Chris
Amen! ;)