Re: Multi Column DECODE?

I agree it is MUCH more intuitive and it fixed my problem

Thanks alot for the help!!!
Brian

Kevin Brand <kevin.brandx_at_tel.gte.com> wrote in message news:9s9hfl$3rh$1_at_news.gte.com...
>
> CASE is new for 8i.
>
> Advantages:
> Much more intuitive syntax
> SQL Standard?
>
> I'm not aware of any disadvantages.
>
> -Kevin
> "Brian McKenzie" <brian_at_stallingscrop.com> wrote in message
> news:jFXF7.34065$7x1.3509347_at_bin4.nnrp.aus1.giganews.com...
> > 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:39:58 CST