# Re: DECODE / CASE Question

From: <fitzjarrell_at_cox.net>
Date: Mon, 2 Jun 2008 12:18:39 -0700 (PDT)

On Jun 2, 2:13 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> On Jun 2, 1:31 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On Jun 2, 12:34 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
>
> > > 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
>
> > > Vince,
>
> > > Your code almost works.  In this case:
>
> > > insert into test values ('A',2,4,6,8);
> > > insert into test values ('B',1,3,5,7);
> > > insert into test values ('C',2,-99999,6,8);
> > > insert into test values ('D',2,-99999,6,-99999);
> > > insert into test values ('E',-99999,-99999,-99999,-99999);
> > > insert into test values ('F',2,-99999,6,7);
>
> > > WITH x AS
> > >  (SELECT c1 as z,
> > >          decode( c2, -99999, 0, c2 ) AS a,
> > >          decode( c3, -99999, 0, c3 ) AS b,
> > >          decode( c4, -99999, 0, c4 ) AS c,
> > >          decode( c5, -99999, 0, c5 ) AS d
> > >   FROM art)
> > > SELECT z, a + b + c - d
> > > FROM x;
>
> > > Z    A+B+C-D
> > > - ----------
> > > A          4 - OK
> > > B          2 - OK
> > > C          0 - OK
> > > D          8 - OK
> > > E          0 - Should be -99999 since all are -99999
> > > F          1 - OK- Hide quoted text -
>
> > > - Show quoted text -
>
> > It may not be the most elegant but it does work:
>
> > SQL> create table art(
> >   2  c1 varchar2(10),
> >   3  c2 number,
> >   4  c3 number,
> >   5  c4 number,
> >   6  c5 number
> >   7  );
>
> > Table created.
>
> > SQL>
> > SQL> insert into art values ('A',2,4,6,8);
>
> > 1 row created.
>
> > SQL> insert into art values ('B',1,3,5,7);
>
> > 1 row created.
>
> > SQL> insert into art values ('C',2,-99999,6,8);
>
> > 1 row created.
>
> > SQL> insert into art values ('D',2,-99999,6,-99999);
>
> > 1 row created.
>
> > SQL> insert into art values ('E',-99999,-99999,-99999,-99999);
>
> > 1 row created.
>
> > SQL> insert into art values ('F',2,-99999,6,7);
>
> > 1 row created.
>
> > SQL>
> > SQL> commit;
>
> > Commit complete.
>
> > SQL>
> > SQL> WITH x AS
> >   2   (SELECT c1 as z,
> >   3           decode( c2, -99999, -0.0000001, c2 ) AS a,
> >   4           decode( c3, -99999, -0.0000001, c3 ) AS b,
> >   5           decode( c4, -99999, -0.0000001, c4 ) AS c,
> >   6           decode( c5, -99999, -0.0000001, c5 ) AS d
> >   7    FROM art)
> >   8  SELECT z,
> >   9     case
> >  10     when a+b+c-d > -0.0000002 then round(a,0)+round(b,0)+round(c,
> > 0)-round(d,0) else -99999 end result
> >  11  FROM x;
>
> > Z              RESULT
> > ---------- ----------
> > A                   4
> > B                   2
> > C                   0
> > D                   8
> > E              -99999
> > F                   1
>
> > 6 rows selected.
>
> > SQL>
>
> > David Fitzjarrell
>
> Thanks David.  Totally dumb here, but the formulas are different:  A +
> B / C or A - B * C / D, etc.
>
> I'm hoping that this will work with everything......- Hide quoted text -
>
> - Show quoted text -

Not according to your example where you complained the initial offering wouldn't suffice, which is what I followed to provide my response. If you want a robust check of the code then provide such an example to emulate.

David Fitzjarrell Received on Mon Jun 02 2008 - 14:18:39 CDT

Original text of this message