Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Decode function to Oracle 7

Re: Decode function to Oracle 7

From: <_at_rennes>
Date: Fri, 15 Oct 2004 15:51:55 +0200
Message-ID: <ckokm4$14t$1@s5.feed.news.oleane.net>


Thanks for this explication

"Mark C. Stock" <mcstockX_at_Xenquery .com> a écrit dans le message de news: HfKdnVJH7ZfyJfLcRVn-pw_at_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 - 08:51:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US