MHajat, I know nothing about Oracle but the 2 functions used (NVL and DECODE) seem simple enough. How they are being used is strange.
DEDCODE is an if, then, Else type structure and can be replace by CASE.
DECODE(expression, search, result (,search, result,...) (, default))
Looking at one DECODE statement decode(totalcost,0,null,totalcost), TOTALCOST is the expression, 0 is the search, NULL is the result, and TOTALCOST is the default. One way to write the CASE would be:
WHEN 0 THEN NULL
NVL checks the first value. If it is NOT NULL it will be returned. If is NOT NULL, the second value will be returned.
Sol,what the entire nested statement is doing is checking 4 columns in sequence. If a column value is NOT 0 use that column value. If it IS 0, use a NULL instead. Then the nested NVL functions finds the first non-null value. IF they are all null return a 0.
I believe this CASE will replace all the NVL and DECODE funciton nesting.
WHEN TOTALCOST <> 0 THEN TOTALCOST
WHEN SUBMITCOST <> 0 THEN SUBMITCOST
WHEN APPRVALUE <> 0 THEN APPRVALUE
WHEN DEFESTVALUE <> 0 THEN DEFESTVALUE
the way it has been written checks for 0's and nulls, if either them two are present, they are ignored, the case statement you have written only checks for 0's? am i right in saying that?
the data we hold isnt exactly great, some fields are 0's and some are nulls, and if either are present, then they need to be ignored, it is a nested NVL statement, i think the NVL equivalent on SQL server is ISNULL, how would i incorporate that into the above code?
WHEN TOTALCOST <> 0
OR TOTALCOST IS NOT NULL THEN TOTALCOST
WHEN SUBMITCOST <> 0
OR SUBMITCOST IS NOT NULL THEN SUBMITCOST
WHEN APPRVALUE <> 0
OR APPRVALUE IS NOT NULL THEN APPRVALUE
WHEN DEFESTVALUE <> 0
OR DEFESTVALUE IS NOT NULL THEN DEFESTVALUE