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 statement

Re: DECODE statement

From: sybrandb <sybrandb_at_gmail.com>
Date: Thu, 07 Jun 2007 04:50:21 -0700
Message-ID: <1181217021.116267.17520@w5g2000hsg.googlegroups.com>


On Jun 7, 1:41 pm, colmkav <colmj..._at_yahoo.co.uk> wrote:
> Hi,
>
> anyone know what I have done wrong in the following code? I get an
> error saying missing right parenthesis but I dont think this is the
> case. Am I using decode wrongly? I get same error if I replace it with
> IFF instead
>
> select "T_EXPOSURE"."VALUE" as "VALUE",
> DECODE("T_EXPOSURETYPE".description='DeltaVal',
> DECODE("T_EXPOSURE".Value>=0,"T_EXPOSURE".Value,0),0) AS DeltaValLong
> from "T_EXPOSURETYPE" "T_EXPOSURETYPE",
> "T_EXPOSURE" "T_EXPOSURE"
the syntax of decode is
decode(<expression>, <alternative1>,<result>, <alternative2>, <result>, <else>)
Consequently decode can only test for equality. Your outer decode should have read
decode(t_exposuretype.description,'DeltaVal',...) Your inner decode should have read
decode(sgn(t_exposure.value),1, "T_EXPOSURE", 0,"T_EXPOSURE",-1,NULL,NULL) If on 9i or higher use the CASE statement. IIF is sqlserver and has no place in Oracle

--
Sybrand Bakker
Senior Oracle DBA
Received on Thu Jun 07 2007 - 06:50:21 CDT

Original text of this message

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