Print Page - Decode function on framework manager

Title: Decode function on framework manager
Post by: mohammed_hajat on 04 Jan 2010 07:04:01 AM

I have a decode sql from oracle, which i need to translate into a measure on Framework Manager.

I know the equivalent is the Case function, but I cant seen to write the case equivalent of the decode, could anyone please assist? not sure how this would be written as a measure,.. can anyone help?

Code Select

nvl(decode(totalcost,0,null,totalcost),
nvl(decode(submitcost,0,null,submitcost),
nvl(decode(apprvalue,0,null,apprvalue),
nvl(decode(defestvalue,0,null,defestvalue),0)))) as "COST"

Title: Re: Decode function on framework manager
Post by: MFGF on 04 Jan 2010 08:53:59 AM

Hi,

You have two functions in fact - NVL and DECODE.

CASE
WHEN [totalcost] <> 0 AND [totalcost] is not null THEN [totalcost]
WHEN [submitcost] <> 0 AND [submitcost] is not null THEN [submitcost]
WHEN [apprvalue] <> 0 AND [apprvalue] is not null THEN [apprvalue]
WHEN [defestvalue] <> 0 AND [defestvalue] is not null THEN [defestvalue]
ELSE 0
END

MF.

Title: Re: Decode function on framework manager
Post by: mohammed_hajat on 04 Jan 2010 10:31:28 AM

thank you.. works nicely...

COALESCE(NULLIF(totalcost,0)
,NULLIF(submitcost,0)
,NULLIF(apprvalue,0)
,NULLIF(defestvalue,0)
,0) "COST"

i tried this way and it works as well... thanks.

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Framework Manager => Topic started by: mohammed_hajat on 04 Jan 2010 07:04:01 AM