I have a PL/SQL procedure in which I am comparing movement of goods. On one side, I have a selected period, and on the other the Year-to-Date. There are going to be times when the period has no movement for a line in which there is movement year-to-date. I need to be able to catch these and treat them like an error condition so they don't bomb out my report, but for some reason even COUNT(JOB_ID) and NVL(COUNT(JOBID,0) both return "no rows returned". I need a zero (0) returned so I can do a comparison and substitute values in there so the report doesn't die. Here is the query, can anyone suggest an alternative?
select NVL(count(j.LOAD_JOB_ID),0) into ld_chk
from TC_LOAD_JOBS j, TC c
where c.DATE_INDEX between to_date('11/01/2008','MM/DD/YYYY') and SYSDATE
and c.TC_ID = j.TC_ID
and j.LOAD_RATE_ID = p_rt
group by j.LOAD_RATE_ID;
For reference, TC is the master table, and TC_LOAD_JOBS is child. The dates are aribtrary and will be replaced by passed in values as soon as I can get this working. p_rt is a passed in value from the open cursor for the line in question.