Re: DECODE / CASE Question

From: <fitzjarrell_at_cox.net>
Date: Mon, 2 Jun 2008 11:31:55 -0700 (PDT)
Message-ID: <a4fb8373-7667-4e57-8207-7e35722c29b4@34g2000hsf.googlegroups.com>

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 Received on Mon Jun 02 2008 - 13:31:55 CDT