CODEWITHSUNDEEP
sqloracle-database
jonny hieu nguyen
jonny hieu nguyen

Reputation: 15

What is the difference between case when and decode?

What is the difference between case when and decode? How do I rank by rank with a piece of code I have

select customer.customerid,customer.contractid,customer.termed,customer.bracnchcode,  
(case    
when customer.contractid<5000000000 then 'A'   
when 5000000000<customer.contractid <=100000000000 then'B'    
else 'C'    
end) as rank   
from customer;

It happens 1 every thing I don't understand

Upvotes: 1

Views: 677

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21085

DECODE can be used if you classifies on the equals condition. E.g.

Customer_id  Rank
1            A
2            B
else         C

You'll write

 DECODE(customer_id,1,'A',2,'B','C')

If you classifies based on a range use CASE WHEN

Customer_id        Rank
1   ..  499        A
500 .. 1000        B
else               C

This would be coded as

CASE WHEN Customer_id < 500  THEN 'A'
     WHEN Customer_id < 1000 THEN 'B'
     ELSE 'C' END as RANK

Note that the case is performed in sequential order of the WHEN parts and on the first true condition you quit, so in the second WHEN you do not need to exculde the lower bound (e.g. with BETWEEN) as this is automaticaly true because the first WHENis not true if you get the the second one.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142968

Your code is invalid; should be something like this:

SELECT customer.customerid,
       customer.contractid,
       customer.termed,
       customer.bracnchcode,
       (CASE
           WHEN customer.contractid < 5000000000
           THEN
              'A'
           WHEN customer.contractid BETWEEN 5000000001 AND 100000000000
           THEN
              'B'
           ELSE
              'C'
        END) AS RANK
  FROM customer;

As of your question regarding DECODE: it would make things way more complex in this case because you'd have to calculate the difference between contractid and boundaries you set check sign of the difference, probably use nested decodes which then becomes difficult to read and even worse to understand. Stick to CASE.

Upvotes: 1

Related Questions