Need Help - Dividing 2 derived fields

[jazcastle]

I am trying to get an average of money paid over a number of checks that had certain earnings codes. So I added the sum of the earnings as one derived field and the the count of the check numbers that meet the critera as the other derived field. They work just fine alone but I want to divide the 2 fields and I get no rows found. Here is the code:
((TO_NUMBER(NVL( (SELECT SUM("PS_AL_CHK_HRS_ERN"."EARNINGS")
FROM
"PS_AL_CHK_HRS_ERN"
WHERE
((("PS_AL_CHK_HRS_ERN"."CHECK_DT" BETWEEN <<BeginDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> AND <<EndDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>) AND
("PS_AL_CHK_HRS_ERN"."ERNCD" IN ('C','43','44','45','46','47'))AND
("PS_AL_CHK_HRS_ERN"."EMPLID" = "PS_JOB"."EMPLID")))),0)))
/
(TO_NUMBER( (SELECT Count(Distinct"PS_AL_CHK_HRS_ERN"."CHECK_NBR")
FROM
"PS_AL_CHK_HRS_ERN"
WHERE
((("PS_AL_CHK_HRS_ERN"."CHECK_DT" BETWEEN <<BeginDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> AND <<EndDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>) AND
("PS_AL_CHK_HRS_ERN"."ERNCD" IN ('C','43','44','45','46','47'))AND
("PS_AL_CHK_HRS_ERN"."EMPLID" = "PS_JOB"."EMPLID")))))))

Please help!

 

[jazcastle]

Nevermind! I got it - it was crashing since it was trying to divide zeros for some people, i guess. I nested it in a case statement:

CASE
WHEN
(TO_NUMBER(NVL( (SELECT SUM("PS_AL_CHK_HRS_ERN"."EARNINGS")
FROM
"PS_AL_CHK_HRS_ERN"
WHERE
((("PS_AL_CHK_HRS_ERN"."CHECK_DT" BETWEEN <<BeginDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> AND <<EndDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>) AND
("PS_AL_CHK_HRS_ERN"."ERNCD" IN ('C','43','44','45','46','47'))AND
("PS_AL_CHK_HRS_ERN"."EMPLID" = "PS_JOB"."EMPLID")))),0)))=0
THEN
0
ELSE
(TO_NUMBER(NVL( (SELECT SUM("PS_AL_CHK_HRS_ERN"."EARNINGS")
FROM
"PS_AL_CHK_HRS_ERN"
WHERE
((("PS_AL_CHK_HRS_ERN"."CHECK_DT" BETWEEN <<BeginDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> AND <<EndDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>) AND
("PS_AL_CHK_HRS_ERN"."ERNCD" IN ('C','43','44','45','46','47'))AND
("PS_AL_CHK_HRS_ERN"."EMPLID" = "PS_JOB"."EMPLID")))),0)))/(TO_NUMBER(NVL( (SELECT Count(Distinct"PS_AL_CHK_HRS_ERN"."CHECK_NBR")
FROM
"PS_AL_CHK_HRS_ERN"
WHERE
((("PS_AL_CHK_HRS_ERN"."CHECK_DT" BETWEEN <<BeginDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> AND <<EndDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>) AND
("PS_AL_CHK_HRS_ERN"."ERNCD" IN ('C','43','44','45','46','47'))AND
("PS_AL_CHK_HRS_ERN"."EMPLID" = "PS_JOB"."EMPLID")))),0)))
END