cursor -urgent

Can some one help me on this ???

my cursor still gives ORA-01427: single-row subquery returns more than one row
ORA-06512: at line 11

Quote:

---

Originally posted by prodadmin
11 update v_jobs
12 set returned_date =(select min(jh.created_date)
13 from jobs j, job_history jh, stages s
14 where s.system_stage_id=8 and
15 JH.JOB_ID=J.JOB_ID AND
16 JH.STAGE_ID=S.STAGE_ID
17 group by jh.job_id);

---

The (select . . . group by jh.job_id) will give as many rows are there are distinct values of jh.job_id hence the error - if you want to do it this way it must return exactly one row.

But the bigger problem is the logic of the whole thing . . . which rows in v_jobs should be updated? I suspect the logic of selecting the rows in v_jobs and the logic of selecting the values to use for the update have got inextricably mixed up. If you can clarify that, it would be clearer how to do this (might not need that cursor at all).

I'm guessing wildly . . . is this something like what you want to do?

1 declare
2 cursor j1 is select min(jh.created_date) mindate, jh.job_id
3 from jobs j, job_history jh, stages s
4 where s.system_stage_id=8 and
5 JH.JOB_ID=J.JOB_ID AND
6 JH.STAGE_ID=S.STAGE_ID
7 group by jh.job_id;
8 -- jobrdate j1%rowtype; not needed?

9 begin

10 for jobrdate in j1 loop

11 update v_jobs v
12 set returned_date = jobrdate.mindate
13 where v.job_id = jobrdate.job_id;

18 end loop;

COMMIT;

19 dbms_output.put_line('returned date has been update');
20 end;

:confused:

Thank you very much.That helped a lot!!

Your help is much much appreciated!!!