Re: case expression to decode function

Actually, I need to transform case into decode in a general way, so the second approach of substracting looks better to me.

Also, here is my attempt to transform a fairly general and complex case statement into a decode statement. Does the translation look good. Any better way to do this.

case when Ca > Cb then col_1

     when Cc = Cd then col_2
     when Ce >= Cf then col_3
     when Cg <> Ch then col_4
     when Ci <= Cj then col_5
     when Ck < Cl then col_6
     when Cm < Cn AND Co > Cp then col_7
     else col_8

translates into:

decode( sign(Ca - Cb), 1, col_1,
        decode(sign(Cc - Cd), 0, col_2,
           decode(sign(Ce - Cf), 1, col_3, 0, col_3,
              decode(sign(Cg - Ch), 1, col_4, -1, col_4,
                 decode(sign(Ci - Cj), -1, col_5, 0, col_5,
                    decode(sign(Ck - Cl), -1, col_6,
                      decode(Cm - Cn, -1, decode(Co - Cp, 1, col_7),
col_8)
                 )
               )
            )
          )
        )

Thanks                    

"nobody" <nobody_at_nowhere.com> wrote in message news:<upLla.7041$BQi.3665_at_news04.bloor.is.net.cable.rogers.com>...
> or just subtract 1 and then use sign.
>
> "db dude" <db_guy_2002_at_yahoo.com> wrote in message
> news:f4a8da28.0304111758.1d29ecf9_at_posting.google.com...
> > SELECT CASE WHEN a > 1 THEN 'boo' WHEN a < 1 then 'hoo' ELSE 'foo' END
> > from X;
> >
> > Oracle 8 does not support case expressions. Can some one give me an
> > oracle 8 select query that does the same thing as the select query
> > above.
> >
> > Thanks
Received on Mon Apr 14 2003 - 19:57:29 CDT