Re: DECODE / CASE Question

From: <amerar_at_iwc.net>
Date: Mon, 2 Jun 2008 07:58:51 -0700 (PDT)
Message-ID: <c15cd57f-a7f6-4d4f-a2c9-589380d3758f@f36g2000hsa.googlegroups.com>


On May 30, 2:44 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> Comments embedded.
> On May 30, 12:51 pm, "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.
>
> Forgive me for being dense, but if the result of all of that math is 0
> why aren't you simply returning the result of all of that math?
> Setting the value to 0 if the result of the calculations is 0 is makes
> what sense? The 'problem' solves itself, doesn't it?
>
> > 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!
>
> David Fitzjarrell

David,

What we're trying to do is see if all the values are -99999. If all the values are -99999, then we are going to return -99999. If ANY single value is -99999 while others are -99999, then we will decode the -99999 into a 0, otherwise we just use the regular formula. Here is an example:

3+5+7+9 = 24 - all is good
3+5+-99999+9 = 14 - -99999 is translated into 0 -99999+-99999+-99999+-99999 = Should return -99999

Does that help? Received on Mon Jun 02 2008 - 09:58:51 CDT

Original text of this message