Re: DECODE / CASE Question

From: <amerar_at_iwc.net>
Date: Mon, 2 Jun 2008 13:04:00 -0700 (PDT)
Message-ID: <5aaaf10b-3b0d-457a-b2a4-038ae0f16814@l42g2000hsc.googlegroups.com>


On Jun 2, 2:18 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> 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

Sorry for not being clear. Here is an example that does not seem to be working right. I probably have to tweak it a bit to get it to work. I'm sure the overall concept is there.......

select updated, RES_EXP, SEL_EXP, DDA from data_holder.zfund_data where m_ticker = 'BIOQ';
WITH x AS
 (SELECT updated, m_ticker,

         decode( RES_EXP, -99999, 0, RES_EXP ) AS a,
         decode( SEL_EXP, -99999, 0, SEL_EXP ) AS b,
         decode( DDA, -99999, 0, DDA ) AS c
  FROM zfund_data
  WHERE m_ticker = 'BIOQ')
SELECT updated, m_ticker,

   case
   when a-b+c > -0.0000002 then round(a,0)+round(b,0)+round(c,0) else -99999 end result
FROM x;

Thanks! Received on Mon Jun 02 2008 - 15:04:00 CDT

Original text of this message