Re: DECODE / CASE Question

From: Vince <vinnyop_at_yahoo.com>
Date: Mon, 2 Jun 2008 08:27:31 -0700 (PDT)
Message-ID: <8d743fa7-400a-4f5f-98b6-ff1efa5d939e@m73g2000hsh.googlegroups.com>


On May 30, 10:51 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> Hi,
>
> Say we have a very long DECODE statement like this:
>
> DECODE(2,-99999,0,2) + DECODE(5,-99999,0,5) + DECODE(7,-99999,0,7) -
> DECODE(8,-99999,0,8)  * DECODE(9,-99999,0,9) stuff
>
> Pardon the use of real numbers, that was for testing.
>
> Now, I want to basically say if the value of this is 0, then 0 should
> be returned, otherwise the result of the math should be returned.
>
> Can I use a case statement for this somehow?  Or do I enclose it
> around another DECODE?  Can I use the generated column alias???
>
> Thanks!

One possibility which doesnt get rid of the decodes, but leaves your formula simple:
WITH x AS
( SELECT decode( a, -999999, 0, a ) AS a,

               decode( b, -999999, 0, b ) AS b ...
FROM ...)
)
SELECT a + b + c - d * e
FROM x Received on Mon Jun 02 2008 - 10:27:31 CDT

Original text of this message