Re: Decode function

Unmesh <unmeshl_at_aol.com> wrote in message news:38147ED7.60F1F559_at_aol.com...
> What if the decode fuction need to be used with list of values using
> between
> operator?
> like say
> select ...
> from ...
> where ...
> order by decode(ITF.ITEM_TYPE, (between 60 and 64) , 0, (between 30 and
> 32) , 1, 45,
> 2, 90, 3, 46, 4, 5);
>
> Any feedback is appreciated..

[Quoted] [Quoted] It can be done by using a complicated math expression. [Quoted] For example, to show 'Yes' if between 60 and 64 or 'No' if not:

SQL> select n, decode(

  2      sign(64-n)*sign(n-60)*(sign(64-n)-sign(n-60)),
  3      0, 'Yes', 'No')

        N DEC
--------- ---

       58 No
       59 No
       60 Yes
       61 Yes
       62 Yes
       63 Yes
       64 Yes
       65 No
       66 No

9 rows selected.

But it is not readable and prone to error. A better way is to create a function:

SQL> create or replace function is_between(n number, x number, y number)   2 return number
  3 is
  4 begin
  5 if n between x and y then
  6 return 1;
  7 else
  8 return 0;
  9 end if;
 10 end;
 11 /

Function created.

SQL> select n, decode(is_between(n, 60, 64), 1, 'Yes', 'No') from test;

        N DEC
--------- ---

       58 No
       59 No
       60 Yes
       61 Yes
       62 Yes
       63 Yes
       64 Yes
       65 No
       66 No

9 rows selected. Received on Tue Oct 26 1999 - 18:49:42 CEST