Convert a value based on range

Question

I need to convert a number to another value based on a range:

ie:

7 = "A"
106 = "I"

I have a range like this:

from to     return-val
1    17     A
17   35     B
35   38     C
38   56     D
56   72     E
72   88     F
88   98     G
98   104    H
104  115    I
115  120    J
120  123    K
123  129    L
129  infinity   M

The values are fixed and do not change.

I was thinking a lookup table would be required, but is there a way it could be done with a function on an analytics function inside of oracle?

Answer 1

Think I would use a mapping table:

mapping:

to  ret
17  A
38  B

Select Max(ret)
From mapping
Where x <= to

You could also use CASE WHEN:

Select Case When x <= 17 Then 'A'
            When x <= 35 Then 'B'
            When x <= 38 Then 'C'
            ...
            Else 'M' End
From your_table

Answer 2

SQL servers are designed such that operating on a table is faster than anything else -- particularly when that table will only have 13 rows.

Answer 3

I would create a function, in oracle, since it should be more efficient than doing a table lookup (no roundtrip to disk will ever be involved).

CREATE OR REPLACE Function ValueFromRange
   ( n IN number )
   RETURN varchar2
IS
  ret varchar2;
BEGIN
ret := -1; -- UNDEFINED?
IF n >= 1 and n <= 17 THEN
   ret := 'A';
ELSIF n >= 18 and n <= 35 THEN
   ret := 'B';
ELSIF n >= 36 and n <= 38 THEN
   ret := 'C';
ELSIF n >= 39 and n <= 56 THEN
   ret := 'D';
ELSIF n >= 57 and n <= 72 THEN
   ret := 'E';
ELSIF n >= 73 and n <= 88 THEN
   ret := 'F';
ELSIF n >= 89 and n <= 98 THEN
   ret := 'G';
ELSIF n >= 99 and n <= 104 THEN
   ret := 'H';
ELSIF n >= 105 and n <= 115 THEN
   ret := 'I';
ELSIF n >= 116 and n <= 120 THEN
   ret := 'J';
ELSIF n >= 121 and n <= 123 THEN
   ret := 'K';
ELSIF n >= 124 and n <= 129 THEN
   ret := 'L';
ELSIF n >= 130 THEN
   ret := 'M';
END IF;
RETURN ret;

END;