Great I have been looking for a case example. As it seems most are going
the DECODE route are there any advantages disadvantges to using CASE?
Thanks for your help
Brian
Kevin Brand <kevin.brandx_at_tel.gte.com> wrote in message
news:9s9e6r$3qd$1_at_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 - 14:23:43 CST
