decode function

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]