# Re: DECODE / CASE Question

Date: Mon, 2 Jun 2008 08:14:49 -0700 (PDT)

Message-ID: <9cb2d262-2f1c-4ec1-a353-40e334032fe4@26g2000hsk.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 - 10:14:49 CDT