Return a value if null

Question

In oracle, I have a piece of code that returns our work order pass rate. It essentially takes the number of failures (MRB) and divides by the number of work orders processed (WO) to give the rate. The problem I have is that unless there is a failure (MRB) I will not get a return for the corresponding month. If there is no MRB failure, I would like a return of 100% for that month. Here is the code I am working with. For the record, I am not a programmer, I've just picked up a bit of info along the way. I have tried adding NVL but that does not seem to help.

Select To_Char(MTH, 'Month') As "Month",WO, MRB,
    Round(1 - (MRB / WO), 3) *      100 As "Pass Rate",
    '98' As Goal
 From 
 (
        Select Trunc(g.START_DATE, 'Month') As mth,
                Count(Distinct V_PDAYPROD_CRW1.PDAYPROD_ID) As WO,
                Count(Distinct V_WF_HEADER_MRB.ID) As MRB
        From GLPERIODS g
        Left Join V_PDAYPROD_CRW1 On Trunc(g.START_DATE, 'Month') = Trunc(V_PDAYPROD_CRW1.PROD_DATE, 'Month')
        Left Join V_WF_HEADER_MRB On Trunc(g.START_DATE, 'Month') = Trunc(V_WF_HEADER_MRB.OPEN_DATE, 'Month')
        Inner Join ARINVT On V_PDAYPROD_CRW1.ARINVT_ID = ARINVT.ID
        Where Extract(Year From g.START_DATE) = Extract(Year From SysDate) 
          And V_WF_HEADER_MRB.WF_TYPE_ID = '99' 
          And V_WF_HEADER_MRB.EPLANT_ID = 2 
          And ARINVT.EPLANT_ID = 2
        Group By Trunc(g.START_DATE, 'Month'),
             V_WF_HEADER_MRB.WF_TYPE_ID
)
Group By To_Char(MTH, 'Month'),WO,MRB,
   Round(1 - (MRB / WO), 3) * 100,
   '98',MTH
Order By MTH

The above code returns the following:

  MONTH     |  WO   |  MRB  |  Pass Rate  | GOAL 
  September |  60   |  1    |    98.3     |  98 
  December  |  30   |  2    |    93.3     |  98

I would like it to return something like this:

  MONTH     |  WO   |  MRB  |  Pass Rate  | GOAL
  January   |  25   |  0    |    100      |  98
  February  |  66   |  0    |    100      |  98
  March     |  35   |  0    |    100      |  98
  April     |  22   |  0    |    100      |  98
  May       |  19   |  0    |    100      |  98
  June      |  47   |  0    |    100      |  98
  July      |  52   |  0    |    100      |  98
  August    |  55   |  0    |    100      |  98
  September |  60   |  1    |    98.3     |  98
  October   |  39   |  0    |    100      |  98
  November  |  18   |  0    |    100      |  98
  December  |  30   |  2    |    93.3     |  98

Thank you for any help you can offer.

Answer 1

If you expect all the records from the main table to be returned in the result set then you should not use the filtering criteria on "v_wf_header_mrb" table in the where clause.

Remove "V_WF_HEADER_MRB.WF_TYPE_ID = '99' And V_WF_HEADER_MRB.EPLANT_ID = 2 " condition from Where clause and try.

You can use the below query instead.

SELECT
    TO_CHAR(mth,'Month') AS "Month",
    wo,
    mrb,
    round(1 - (mrb / wo),3) * 100 AS "Pass Rate",
    '98' AS goal
FROM
    (
        SELECT
            trunc(g.start_date,'Month') AS mth,
            COUNT(DISTINCT v_pdayprod_crw1.pdayprod_id) AS wo,
            COUNT(DISTINCT v_wf_header_mrb.id) AS mrb
        FROM
            glperiods g
        LEFT JOIN
            v_pdayprod_crw1
        ON
            trunc(g.start_date,'Month') = trunc(v_pdayprod_crw1.prod_date,'Month')
        LEFT JOIN
            v_wf_header_mrb
        ON
            trunc(g.start_date,'Month') = trunc(v_wf_header_mrb.open_date,'Month')
        AND
            v_wf_header_mrb.wf_type_id = '99'
        AND
            v_wf_header_mrb.eplant_id = 2
            INNER JOIN
                arinvt
            ON
                v_pdayprod_crw1.arinvt_id = arinvt.id
        WHERE
            EXTRACT(YEAR FROM g.start_date) = EXTRACT(YEAR FROM SYSDATE)
        AND
            arinvt.eplant_id = 2
        GROUP BY
            trunc(g.start_date,'Month'),
            v_wf_header_mrb.wf_type_id
    )
GROUP BY
    TO_CHAR(mth,'Month'),
    wo,
    mrb,
    round(1 - (mrb / wo),3) * 100,
    '98',
    mth
ORDER BY mth;