Re: DECODE with a range values

"Hagai Katz" <katzh_at_bgumail.bgu.ac.il> a écrit dans le message news: 9iuq84$i0f$1_at_news.huji.ac.il...
> Hi all.
> I wondered if anyone could explain and give an example how to a DECODE with
> a range of values and with multiple values, for example if I want to
> translate the following:
> 0=A
> 1,2=B
> 3-5=C
> 6-10=D
> 11-50=E
> 51- ... =F
> Thanks,
> Hagai.
>
>

If you are in 8.1.6 and up you can use "case": SQL> select col,

  2         case when (col=0) then 'A'
  3              when (col in (1,2)) then 'B'
  4              when (col between 3 and 5) then 'C'
  5              when (col between 6 and 10) then 'D'
  6              when (col between 11 and 50) then 'E'
  7              else 'F'
  8         end col

       COL C
---------- -

         0 A
         1 B
         2 B
         4 C
         8 D
        16 E
        32 E
        64 F

8 rows selected.

Otherwise with "decode":

SQL> select col,

  2         decode(sign(col-50), 1, 'F',
  3         decode(sign(col-10), 1, 'E',
  4         decode(sign(col-5), 1, 'D',
  5         decode(sign(col-2), 1, 'C',
  6         decode(sign(col), 1, 'B',
  7                'A'))))) col

       COL C
---------- -

         0 A
         1 B
         2 B
         4 C
         8 D
        16 E
        32 E
        64 F

8 rows selected.

--
Have a nice day
Michel