Re: DECODE, CASE or perhaps a suggestion?

"Brian McKenzie" <brian_at_stallingscrop.com> wrote in message news:ylCF7.67417$U7.5570247_at_bin1.nnrp.aus1.giganews.com...
> Hello All
>
> I have the following expression in a crystal reports report against an
> Oracle
> 8i Databse.
>
> <----SNIP---->
> IF {COMMISSION_01.POLICYTYPE} = "MPCI" Then
> IF {COMMISSION_01.ISCAT} = "No" Then
> IF {COMMISSION_01.ISCANCELLED} = "No" Then
> "MPCI"
> Else
> "CANCELLED"
> Else
> "CAT"
> Else
> "CRC"
> <----SNIP---->
>
> PolicyType Column contains 2 values MPCI,CRC
> ISCAT has 2 values Yes, No
> ISCANCELLED has 2 values Yes, NO
>
> New column will have an End result with 4 values MPCI, CRC, CAT, CANCELLED
>
> I am not sure I am explaining this properly, tell me so and I will be
happy
> to expand
>
> Should I use CASE, DECODE or am I barking at the wrong trees. I have drug
> out the manuals and scoured some newsgroups, but I just cant seem to get
> this one. Any suggestions or referances?
>
> Thanks
> Brian
>
>
>

decode(COMMISSION_01.POLICYTYPE,

            'MRCP' ,
decode(COMMISSION_01.ISCAT,'No',decode(COMMISSION_01.ISCANCELLED,'No','MRCP' ,'CANCELLED'),'CAT')
            'CRC' ) Apart from the clumsy formatting the decode solution is fairly straightforward.
As long as no ranges and greater than/lesser than is involved, I would prefer decode

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address