Multi Column DECODE?

From: Brian McKenzie <mckenzb_at_altavista.net>
Date: 6 Nov 2001 10:44:51 -0800
Message-ID: <a22c9fca.0111061044.19ba757d@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 Tue Nov 06 2001 - 12:44:51 CST