Decode function on framework manager

[mohammed_hajat]

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 Expand

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"

[MFGF]

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.

[mohammed_hajat]

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.