Re: DECODE statement

On Jun 7, 8:54 am, colmkav <colmj..._at_yahoo.co.uk> wrote:
> On 7 Jun, 15:09, sybrandb <sybra..._at_gmail.com> wrote:
>
>
>
>
>
> > On Jun 7, 3:01 pm, colmkav <colmj..._at_yahoo.co.uk> wrote:
>
> > > On 7 Jun, 13:50, sybrandb <sybra..._at_gmail.com> wrote:
>
> > > > 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
>
> > > I am using Oracle XE which doesnt seem to recognise this "SGN"
> > > function. Is there an alternative?- Hide quoted text -
>
> > > - Show quoted text -
>
> > The function appears to be SIGN, and it still exists in 10g.
> > The alternative I already stated
> > </quote>> > If on 9i or higher use the CASE statement.
> > </end quote>
> > Is that unclear? Didn't you understand it, or didn't you read it?
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBA- Hide quoted text -
>
> > - Show quoted text -
>
> Case instead of what? Decode? Yeah I tried SIGN and the code works
> fine now.- Hide quoted text -
>
> - Show quoted text -

Hi All,

   I am responding only to tell the mistake in the DECODE statement. Not bothered about the rest. Here it goes :

select "T_EXPOSURE"."VALUE" as "VALUE"

          ,
DECODE("T_EXPOSURETYPE".description='DeltaVal',DECODE("T_EXPOSURE".Value>=0,"T_EXPOSURE".Value, 0),0,0) AS DeltaValLong
 from "T_EXPOSURETYPE" "T_EXPOSURETYPE",

         "T_EXPOSURE" "T_EXPOSURE" ====> ,0 before the closing parenthses of the main decode is missing.

Hope it helps.

• I AM A SLOW WALKER BUT I NEVER WALK BACKWARDS===