Re: Multi Column DECODE?

Gerard
I guess you are right. I thought this would be quick and easy but have been 2days so far (NEWBIE woes) Basically I am just triing to get the Policy type column to be as shown and I see your point here. As for decision the only 2 that would be added are MPCI | YES | YES would be decided CANCELLED and the other would be CRC | YES | YES would be decided CANCELLED. CRC can never be CAT so those variables can be dicarded. I figured if I could get the first 4 I could wing the rest on my own, you know a glutton for punishment.

Thanks for the help!

Gerard H. Pille <ghp_at_skynet.be> wrote in message news:3BE83C51.EE448B5A_at_skynet.be...
> Brian McKenzie wrote:
> >
> > 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)
>
> But what with the other combinations? Your decision table is lacking a
> lot.
>
> select policytype, iscat, iscancelled,
> decode(policytype,
> 'MPCI',decode(iscat,
> 'NO', decode(iscancelled,'NO','MPCI',
> 'CANCELLED'),
> decode(iscancelled,'NO', 'CAT',
> '?????????')),
> 'CRC', decode(iscat,
> 'NO', decode(iscancelled,'NO','CRC',
> '?????????'),
> '?????????'),
> '?????????')
> from coverage_01;
>
> >
> > POLICYTYPE ISCAT ISCANCELLED = NewColumn
> > --------------------------------------------------------
> > MPCI NO NO MPCI
> > MPCI NO YES CANCELLED
> > MPCI YES NO CAT
> > CRC NO NO CRC
> >
> > 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
>
> --
>
> Gerard H. Pille
>
> (denk even na voor je antwoordt)
>
Received on Tue Nov 06 2001 - 14:21:15 CST