Re: "decode" the boolean value retruned from a stored function

Vahidt A. Tadjkarimi wrote:
>
> I am trying to use the "decode" fuction to interpreat a returned
> bololean value from a store function to a char, but not much luck!
> Here is the example:
>
> create or replace function PLUS_MINUS_BO (VAL1 in number, VAL2 in
> number) return boolean
> is
> begin
> retrun ((abs(val1 - val2)/val1) > .2);
> end PLUS_MINUS_BO;
> /
>
> Now, when I execute the following "select":
>
> sql> select decode(PLUS_MINUS_BO(2,3), TRUE, '+', '-') from dual;
>
> I get:
>
> ERROR at line 1:
> ORA-00904: invalide column name
>
> Any hints?!

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Last time I checked, TRUE wasn't a column in DUAL. You can, however select a literal 'TRUE' from dual, but that won't work for your purposes.

Booleans are PL/SQL datatypes, and therefore aren't applicable outside of a PL/SQL block. (SQL*Plus won't know anything about them and will therefore interperet TRUE or FALSE as a column name.

Easy workaround:

create or replace function PLUS_MINUS_BO (VAL1 in number, VAL2 in number) return char
is
  result char(1);
begin
  if (abs(val1 - val2)/val1) > .2 then

     result := '+';
  else

     result := '-';
  end if;
  return result;
end PLUS_MINUS_BO;
/

sql> select PLUS_MINUS_BO(2,3) from dual;

Ken Shirey
Oracle Database Administrator
PrimeCo Personal Communications, LLP
kshirey_at_primeco.com
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Received on Fri Nov 15 1996 - 00:00:00 CET