Re: Multi Column DECODE?

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Tue, 06 Nov 2001 20:38:57 +0100
Message-ID: <3BE83C51.EE448B5A@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 - 13:38:57 CST