CODEWITHSUNDEEP
oracle-database
MB34
MB34

Reputation: 4424

Oracle calculations within Crystal report

I have this code in a Crystal report. It uses 2 fields, st.pass_total and st.fail_total to calculate the pass ratio. I'd like to replace this Crystal code with PL/SQL code to return just the pass_ratio:

if isnull({st.PASS_TOTAL}) 
   and isnull({st.FAIL_TOTAL}) then pass_ratio:=""
else if (not isnull({st.PASS_TOTAL})) 
        and isnull({st.FAIL_TOTAL}) then pass_ratio:="100%"
else if (isnull({st.PASS_TOTAL})
           or {st.PASS_TOTAL}=0) 
         and (not isnull({st.FAIL_TOTAL})) then pass_ratio:=""
else pass_ratio:=totext({st.PASS_TOTAL}/({st.PASS_TOTAL}+{st.FAIL_TOTAL})*100)+"%";

This is what I have in PL/SQL, is it correct?

decode((is_null(st.pass_total) AND is_null(st.fail_total)), "",
       (not is_null(st.pass_total) AND not is_null(st.fail_total)), "100%",
       ((is_null(st.pass_total) OR st.pass_total=0) && not is_null(st.fail_total)), "",
       (st.pass_total/(st.pass_total+st.fail_total)*100)||"%"))

I also have one that "calculates" the Cutoff value:

if {e.eve_cutoff}=0
   or isnull({e.eve_cutoff}) then event_cutoff:="140"
else if {e.eve_cutoff}>0 then event_cutoff:=totext({e.eve_cutoff},0);

This is what I have in PL/SQL, is it correct?

decode(e.eve_cutoff, 0, "140",
       e.eve_cutoff, NULL, "140",
       eve_cutoff)

Upvotes: 1

Views: 32

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Your decode statements have several issues. This syntax can be greatly simplified by using function nvl():

select 
    case 
      when nvl(st.pass_total, 0) = 0 then ''
      else 100 * st.pass_total / (st.pass_total + nvl(st.fail_total, 0)) ||'%' 
    end ratio
  from st

and:

select decode(nvl(eve_cutoff, 0), 0, '140', eve_cutoff) cutoff from e

[SQLFiddle1] . [SQLFiddle2]

For first select you may also want to round values with function round(), like I did in SQLFiddle - (if you do not do it you may get overflow error in report).

Upvotes: 1

Related Questions