> -----Original Message-----
> From: Leslie Lu [mailto:[EMAIL PROTECTED]]
>
> Can decode work on a range, like if Code is > 100 and
> < 200, then name is A; if code>200 and code<300, then
> name is B; .... I have about 20 ranges to check. If
> decode cannot handle that, what's an easy way to do
> that?
Use decode in conjunction with sign function. sign (x - y) is 1 if x > y, 0 if x = y, -1 if x < y
example:
LQS> select
2 name,
3 to_char (gpa, '9.9') as gpa,
4 decode (sign (gpa - 4.0), 0, 'A', 1, 'A+',
5 decode (sign (gpa - 3.0), 0, 'B', 1, 'B',
6 decode (sign (gpa - 2.0), 0, 'C', 1, 'C',
7 decode (sign (gpa - 1.0), 0, 'D', 1, 'D', 'F')
8 )
9 )
10 ) as letter_grade
11 from student ;
NAME GPA LE
------------------------------ ---- --
Einstein 5.0 A+
Smith 4.0 A
Jones 3.5 B
Mr. Chance 1.3 D
