On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Tuesday, October 21, 2008

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;

No comments:

Post a Comment