Re: Multi Column DECODE?

From: Kevin Brand <kevin.brandx_at_tel.gte.com>
Date: Tue, 6 Nov 2001 13:40:43 -0600
Message-ID: <9s9e6r$3qd$1@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 Tue Nov 06 2001 - 13:40:43 CST