# Re: DECODE / CASE Question

From: <amerar_at_iwc.net>
Date: Mon, 2 Jun 2008 12:13:21 -0700 (PDT)

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...... Received on Mon Jun 02 2008 - 14:13:21 CDT

Original text of this message