Case vs. Decode

The DECODE and CASE statements can generate the same report as shown on the right. The Case statement (which has two variations) is newer and has several advantages over the Decode.

• offers more flexibility and logical power (see Case Examples)
• easier to read than DECODE
• offers better performance as well

DECODE(value,if1,then1[,if2,then2,]...,else)

• value can be any column or the result of a computation, a substring, etc.

Select Grade,
               Decode(Grade,2 , 'Needs a Raise' ,
                            3 , 'Average Pay'   ,
                            4 , 'Almost There'  ,
                            5 , 'Upper Echelon' ,   '?')
               As
      Grade_Desc
 From SalGrade;

CASE WHEN condition_1 THEN result_1
     WHEN condition_2 THEN result_2
     ...
     ELSE else_result
END
 Select Ename, Grade,
                CASE WHEN Grade = 2      THEN 'Needs a Raise'
                     WHEN Grade = 3      THEN 'Average Pay'
                     WHEN Grade = 4      THEN 'Almost There'
                     WHEN EName = 'KING' Then 'King of the Hill'
                     WHEN Grade = 5      THEN 'Upper Echelon'
                     ELSE                '?'
                 END
       Grade_Desc
  From SalGrade , Emp      
 Where Sal Between LoSal and HiSal
/

---

- OR -

CASE expression WHEN value_1 THEN result_1
                WHEN value_2 THEN result_2
                ...
                ELSE else_result
END

Select Grade,
               CASE Grade When 2 THEN 'Needs a Raise'
                          When 3 THEN 'Average Pay'
                          When 4 THEN 'Almost There'
                          When 5 THEN 'Upper Echelon'
                    ELSE               '?'
                END
      Grade_Desc
 From SalGrade;