Re: Decode function to Oracle 7

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Fri, 15 Oct 2004 07:45:34 -0400
Message-ID: <HfKdnVJH7ZfyJfLcRVn-pw@comcast.com>

"@rennes" <l.sass_at_omp-transport.com> wrote in message news:ckm2ln$46f$1_at_s5.feed.news.oleane.net...
| Great, thanks
|
|
| "Frank Piron" <empty_at_zero.nil> a écrit dans le message de news:
| opsfu26ty0m0et4w_at_news.online.de...
| > Am Thu, 14 Oct 2004 15:02:42 +0200 schrieb @rennes
| > <l.sass_at_omp-transport.com>:
| >
| > > I have that
| > >
| > > select
| > >
| > > decode(myNumberField > 0,
| > > True, 'toto',
| > > False, 'titi') MYRESULT
| > >
| > > from RogerTable
| > >
| > > Oracle 7 say "missing right parentheses" in the > caractere.
| > >
| > > How could i make this test in oracle 7
| >
| > Try:
| >
| > decode(sign(myNumberField),
| > -- case > 0
| > 1, 'toto',
| > -- default value
| > 'titi') MYRESULT
| > Frank
| >
| > --
| > Crisis? What Crisis?
| > ----------------------------------------------------
| > f_r_a_n_k_a_t_k_o_n_a_d_d_o_t_n_e_t
|
|

fyi -- the problem is that oracle does not support boolean values in SQL -- not in 7, 8, 8i, 9i,. or 10g:

create or replace function is_true return boolean is
begin
 return true;
end;
/

select is_true from dual;
/

ORA-06552: PL/SQL: Statement ignored ORA-06553: PLS-382: expression is of wrong type

create or replace function is_true return varchar2 is
begin
 return 'T';
end;
/

select is_true from dual;
/

IS_TRUE

---

'T'

however in 9i, 10g you can use the CASE statement with expressions within SQL: select
 empno

,ename
,comm
,case

 when comm > 500
 then 'Big'
 when comm is null
 then 'None'
 else 'Normal'
 end as comm_size
from emp
/

EMPNO ENAME COMM COMM_SIZE
7839 KING (null) None

7698    BLAKE    (null)    None
7782    CLARK    (null)    None
7566    JONES    (null)    None
7788    SCOTT    (null)    None

7902 FORD (null) None
7369 SMITH (null) None
7499 ALLEN 300 Normal
7521 WARD 500 Normal
7654 MARTIN 1400 Big
7844 TURNER 0 Normal
7876 ADAMS (null) None
7900 JAMES (null) None
7934 MILLER (null) None

++ mcs Received on Fri Oct 15 2004 - 06:45:34 CDT