Re: Multi Column DECODE?

Well, it doesn't seem to work in Oracle 7. Other than that *I* don't know-we're just in the process of moving to 8i. So one drawback is lack of portability-which may or may not be important to you.

Brian McKenzie wrote:

> 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:36:22 CST