Re: DECODE / CASE Question
Date: Mon, 2 Jun 2008 08:59:49 -0700 (PDT)
On Jun 2, 10:27 am, Vince <vinn..._at_yahoo.com> wrote:
> 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
SQL> WITH x AS
(SELECT decode( a, -999999, 0, a ) AS a,
decode( b, -999999, 0, b ) AS b
SELECT c2 + c3 + c4 - c5
decode( b, -999999, 0, b ) AS b *
ERROR at line 3:
ORA-00904: "B": invalid identifier
Did I make some error??
NOTE: Not sure why my post appeared 3 times, I only hit post once......
Thanks! Received on Mon Jun 02 2008 - 10:59:49 CDT