Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Multi Column DECODE?

Re: Multi Column DECODE?

From: Jason <foucault_at_pobox.com>
Date: Sat, 10 Nov 2001 06:55:40 GMT
Message-ID: <Mb4H7.198370$5A3.74195767@news1.rdc2.pa.home.com>


i realize you've already solved this problem, but unless there are more permutations than what you've listed, from my observations what you are really trying to decode are the iscancelled and iscat columns rather than your policytype. this simplifies it quite a bit... unless i've missed one of the permutations in your postings:

set newcolumn = decode(iscancelled,'YES','CANCELLED',

    --if it's cancelled, it's cancelled, end of story decode(iscat,'YES','CAT',policytype)

    --if it's not cancelled but cat'd, write CAT and get on with your life, otherwise just echo the policytype

you said CRC can never result in CAT, but if it's true to also say that iscat then for CRC will always be NO then it doesn't matter what the policytype is. you said MPCI YES YES would be determined as CANCELLED which leads me to believe that cancelled overrides cat, thus i put the decode of ISCANCELLED first. even though CASE may be a wonderful and powerful tool is it really necessary for this job? please don't take offense, just suggesting that sometimes when we approach a problem we think like humans rather than machines.

i think this exercise is still valuable as there may be many cases (pardon me) where case is appropriate and it's good to know it. and certainly you may choose to use it here, but unless it is possible to have data in your columns such as CRC YES NO which should result in an answer of CRC decoding the iscat and iscancelled columns seems a much simpler solution to your particular query and makes the more elaborate CASE statements unnecessary. would you agree or have i underestimated the complexity of permutations among your 3 columns of input data?

"kkrause" <kkrause_at_homesourcecapital.com> wrote in message news:8f31cf59.0111090654.526a4df6_at_posting.google.com...
> I would definitely recommend getting comfortable with CASE. It's very
> powerful and has the advantage of being ANSI SQL-99 compliant.
>
> "Brian McKenzie" <brian_at_stallingscrop.com> wrote in message
news:<jFXF7.34065$7x1.3509347_at_bin4.nnrp.aus1.giganews.com>...
> > Great I have been looking for a case example. As it seems most are
going
> > the DECODE route are there any advantages disadvantges to using CASE?
> >
> > Thanks for your help
> > Brian
> > Kevin Brand <kevin.brandx_at_tel.gte.com> wrote in message
> > news:9s9e6r$3qd$1_at_news.gte.com...
> > >
> > > Assuming 8i, you can use CASE instead of DECODE like so...
> > >
> > > Contents of file case.sql...
> > > /*create table pol ( policytype varchar2(5),
> > > iscat varchar2(3),
> > > iscancelled varchar2(3)
> > > );
> > > insert into pol values (
> > > 'MPCI','YES','NO' );
> > > insert into pol values (
> > > 'CRC','NO','NO' );
> > > insert into pol values (
> > > 'MPCI','NO','NO' );
> > > insert into pol values (
> > > 'MPCI','NO','YES' ); */
> > >
> > > select
> > > policytype,iscat,iscancelled,
> > > case
> > > when policytype = 'MPCI' AND iscat = 'YES' AND iscancelled = 'NO'
> > > THEN 'CAT'
> > > when policytype = 'CRC' AND iscat = 'NO' AND iscancelled = 'NO'
> > > THEN 'CRC'
> > > when policytype = 'MPCI' AND iscat = 'NO' AND iscancelled = 'NO'
> > > THEN 'MPCI'
> > > when policytype = 'MPCI' AND iscat = 'NO' AND iscancelled = 'YES'
> > > THEN 'cancelled'
> > > else 'UNDEFINED'
> > > end "NEWCOLUMN"
> > > from pol
> > > ;
> > >
> > > SVRMGR> @case
> > > POLIC ISC ISC NEWCOLUMN
> > > ----- --- --- ---------
> > > MPCI YES NO CAT
> > > CRC NO NO CRC
> > > MPCI NO NO MPCI
> > > MPCI NO YES cancelled
> > > 4 rows selected.
> > > SVRMGR>
> > >
> > > Hth
> > >
> > > -Kevin
> > >
> > >
> > > "Brian McKenzie" <mckenzb_at_altavista.net> wrote in message
> > > news:a22c9fca.0111061044.19ba757d_at_posting.google.com...
> > > > I have a table containing 3 columns.
> > > >
> > > > POLICYTYPE ISCAT ISCANCELLED
> > > > -------------------------------
> > > > MPCI YES NO
> > > > CRC NO NO
> > > > MPCI NO NO
> > > > MPCI NO YES
> > > >
> > > > I would like to create a query that gives me the following results
(if
> > > > Policy = MPCI ISCAT = YES and cancelled=no then newcolumn=CAT. IF
> > > > policy = CRC, CAT=NO, cancelled=NO then newcolumn=CRC. IF Policy =
> > > > MPCI, CAT = NO cancelled=NO then newcolumn=MPCI. if policy = MPCI,
CAT
> > > > = NO, cancelled=YES then newcolumn=cancelled)
> > > >
> > > > POLICYTYPE ISCAT ISCANCELLED = NewColumn
> > > > --------------------------------------------------------
> > > > MPCI YES NO CAT
> > > > CRC NO NO CRC
> > > > MPCI NO NO MPCI
> > > > MPCI NO YES CANCELLED
> > > >
> > > > I have tried the following suggestion but it does not give me the
> > > > desired results.
> > > >
> > > > select coverage_01.policytype
> > > > decode(COVERAGE_01.POLICYTYPE, 'MPCI',
> > > > decode(COVERAGE_01.ISCAT,'No',
> > > > decode(COVERAGE_01.ISCANCELLED,'No','MPCI',
> > > > 'CANCELLED'),
> > > > 'CAT'),
> > > > 'CRC')
> > > > from coverage_01
> > > >
> > > > If someone could point me in the right direction I sure would
> > > > appreciate it.
> > > >
> > > > Brian
> > >
> > >
> > >
Received on Sat Nov 10 2001 - 00:55:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US