Thanks Lisa I will give this a try!
Brian
Lisa McGrath <lmcgrath_at_uwsa.edu> wrote in message
news:3BE835B0.B6B905E2_at_uwsa.edu...
> We've gotten this to work by concatenating multiple fields into one for
> the decode. For example:
>
> Decode(POLICYTYPE||ISCAT||ISCANCELLED,'MPCIYESNO','CAT,'CRCNONO','CRC'
> etc.)
>
> HTH,
> Lisa
>
> 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)
> >
> > 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 Tue Nov 06 2001 - 14:07:07 CST
