Re: Multi Column DECODE?

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 - 13:10:40 CST