Re: Decode function

Thanks Guys,
Created a function and is working well.

Many thanks,
Unmesh

fumi wrote:

> 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..
>
> It can be done by using a complicated math expression.
> 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')
> 4 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.
>
> 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 Wed Oct 27 1999 - 19:48:23 CEST