multiple cursor with date handling issue reg

[laxman]

Dear all,
i am using three cursors in one pl/sql block each of this three cursor will fetch data based on column of date data type.i believe due to each condition relate to same table,contradiction may arise i.e

1> when code will execute , cursor c1 will do the job perfectly but c2 & c3 here may not be able to solve the purpose since the job has already been done by cursor c1 which i am not sure.my doubts i need all cursor to work based on each condition.
any suggestion from your side could sort out this issues.

Thanks n regards
Laxman

declare
 
begin
 
 FOR c1 IN(SELECT reqid FROM request WHERE lastmoddate<sysdate - 8/24 AND statuscode=1 AND assigned_personid IS NOT NULL)loop
                     
                     UPDATE srequest SET STATUS=open WHERE reqid=c1.reqid;
                     commit;
          
          end loop;
 
 FOR c2 IN(SELECT reqid FROM request WHERE lastmoddate<sysdate - 1 AND statuscode=1 AND assigned_personid IS NULL)loop
                     
                     UPDATE srequest SET STATUS=open WHERE reqid=c2.reqid;
                     commit;
          
          end loop;
 
 FOR c3 IN(SELECT reqid FROM request WHERE lastmoddate<sysdate - 14 AND statuscode=1 AND assigned_personid IS NULL)loop
                     
                     UPDATE srequest SET STATUS=open WHERE reqid=c3.reqid;
                     commit;
          
          end loop;
 
end;

[zargon]

Dear all,
i am using three cursors in one pl/sql block each of this three cursor will fetch data based on column of date data type.i believe due to each condition relate to same table,contradiction may arise i.e

1> when code will execute , cursor c1 will do the job perfectly but c2 & c3 here may not be able to solve the purpose since the job has already been done by cursor c1 which i am not sure.my doubts i need all cursor to work based on each condition.
any suggestion from your side could sort out this issues.

Thanks n regards
Laxman

declare
 
begin
 
 FOR c1 IN(SELECT reqid FROM request WHERE lastmoddate<sysdate - 8/24 AND statuscode=1 AND assigned_personid IS NOT NULL)loop
                     
                     UPDATE srequest SET STATUS=open WHERE reqid=c1.reqid;
                     commit;
          
          end loop;
 
 FOR c2 IN(SELECT reqid FROM request WHERE lastmoddate<sysdate - 1 AND statuscode=1 AND assigned_personid IS NULL)loop
                     
                     UPDATE srequest SET STATUS=open WHERE reqid=c2.reqid;
                     commit;
          
          end loop;
 
 FOR c3 IN(SELECT reqid FROM request WHERE lastmoddate<sysdate - 14 AND statuscode=1 AND assigned_personid IS NULL)loop
                     
                     UPDATE srequest SET STATUS=open WHERE reqid=c3.reqid;
                     commit;
          
          end loop;
 
end;

The real problem with this code is the commits inside loops. Rewrite the code like this:

begin
 
 FOR c1 IN(SELECT reqid FROM request WHERE lastmoddate<sysdate - 8/24 AND statuscode=1 AND assigned_personid IS NOT NULL)loop
                     
                     UPDATE srequest SET STATUS=open WHERE reqid=c1.reqid;
          
          end loop;
 
commit;
 
 FOR c2 IN(SELECT reqid FROM request WHERE lastmoddate<sysdate - 1 AND statuscode=1 AND assigned_personid IS NULL)loop
                     
                     UPDATE srequest SET STATUS=open WHERE reqid=c2.reqid;
          
          end loop;
 
commit;
 
 FOR c3 IN(SELECT reqid FROM request WHERE lastmoddate<sysdate - 14 AND statuscode=1 AND assigned_personid IS NULL)loop
                     
                     UPDATE srequest SET STATUS=open WHERE reqid=c3.reqid;
          
          end loop;
 
commit;
 
end;
 /

As for your concern that the cursors 'may not be able to solve the purpose' I wonder why you think such things. I suggest you run the queries in sequence and note that they do, indeed, 'solve the purpose'.

[laxman]

Thanks sir for ur kind responce,yes i agree this will definitly work,what my intention was that all the three cursor are doing the same job based on different condition.if u can see c2 and c3 .

cursor c2---> lastmoddate< sysdate - 1

cursor c3 ---> lastmoddate< sysdate - 14

job of cursor c3 is already been done by c2 since sysdate - 14 < sysdate - 1,this may cause a replicate of data when c3 will start execute.what i need here is in order to avoid the replicate of data can i merge all the job into single cursor ??? will it work !!!

need ur suggestion kindly

Regards
Laxman

[rajavu]

It could be merged as


begin

FOR c IN(SELECT reqid 
          FROM request 
         WHERE statuscode=1 
           AND (( assigned_personid IS NULL  AND ( sysdate - 1 OR lastmoddate<sysdate - 14  )) 
                OR
                ( assigned_personid IS NOT NULL  AND ( lastmoddate<sysdate - 8/24 )))
         )
               loop
                     
                     UPDATE srequest SET STATUS='open' WHERE reqid=c.reqid;
          
          end loop;
 
commit;

Or using straight SQL as follows

UPDATE  request 
   SET  STATUS='Open'
 WHERE statuscode=1 
   AND (( assigned_personid IS NULL  AND ( sysdate - 1 OR lastmoddate<sysdate - 14  )) 
        OR
        ( assigned_personid IS NOT NULL  AND ( lastmoddate<sysdate - 8/24 )));