Re: DECODE question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 6 Jul 2001 13:57:52 -0700
Message-ID: <9i58og02226@drn.newsguy.com>

In article <9i4vcp$u2o$1_at_newsg4.svr.pol.co.uk>, "Jason" says...
>
>Having been used to Access and SQLServer, I am hitting a couple of problems
>translating some SQL code to Oracle. Here's one that I can't seem to work
>out.
>
>Consider the following Access code. Basically, it looks at a field called
>RotaShift. If this is filled in, that is the field that is used, otherwise
>the field AltShift is used.
>
>IIf ( RotaShift IS NULL, AltShift, RotaShift) AS Shift
>
>Now in trying to translate this to Oracle, I have come up with :-
>
>DECODE (RotaShift, Null, AltShift, RotaShift) AS Shift
>
>However, this doesn't seem to work. From what I can see, the 3rd and 4th
>arguments can only be numeric values, not fields. Is there a workaround for
>this.
>
>

why doesn't it work? getting an error?

Decode can return any type -- if altshift and rotashift are different types you might have to 'to_xxxx' the return type:

scott_at_ORA8I.WORLD> select decode( comm, null, ename, to_char( comm ) ) from emp;

DECODE(COMM,NULL,ENAME,TO_CHAR(COMM))

SMITH
300
500
JONES
1400
BLAKE
CLARK
SCOTT
KING
0
ADAMS
JAMES
FORD
miller

14 rows selected.

Although, NVL might be easier for you (an "IF NULL THEN" function)

scott_at_ORA8I.WORLD> select nvl(to_char(comm), ename ) from emp;

NVL(TO_CHAR(COMM),ENAME)

SMITH
300
500
JONES
1400
BLAKE
CLARK
SCOTT
KING
0
ADAMS
JAMES
FORD
miller

14 rows selected.

>TIA
>
>
>Jason.
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Fri Jul 06 2001 - 15:57:52 CDT