Re: DECODE / CASE Question

From: <amerar_at_iwc.net>
Date: Mon, 2 Jun 2008 10:14:50 -0700 (PDT)
Message-ID: <ea533dae-a0e1-4827-b0d1-fbdeb07fa1da@x35g2000hsb.googlegroups.com>

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 solution works pretty well. I have a question though. Please look at this:

create table test (
  c1 varchar2(1),

  c2   number,
  c3   number,
  c4   number,

  c5 number);

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 test)
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 return -99999
F          1 - OK

I also tried this:

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, CASE when (a+b+c-d) >= 0 THEN 'GREATER'

                else 'LESS'
       END

FROM x;

CASEWHE

---

A GREATER
B GREATER
C LESS
D GREATER
E LESS
F LESS Seems that things are not working. The only one that should have a LESS is E. C should return GREATER because it is 0, and F should return 0 also. The -99999 are decoded into 0. But, E needs to be decoded into -99999 because all of the values are -99999.

So, still working on it.....

Thanks! Received on Mon Jun 02 2008 - 12:14:50 CDT