All Forums General
zbynekk 1 post Joined 06/11
04 Nov 2011
WHY SQLSTATE = ' 02000' in cursor does not work properly

 

hello, 

can somebody tell me why SQLSTATE = ' 02000'  does not work? The last update is running in a cycle. how can I interrupt the last update sql, all update statements before the last has run fine.

 

----------

replace  procedure dw_prod.update_F_DL_APviews_zu() 

 

BEGIN

 

DECLARE varNameOfView  varchar(30);

DECLARE varNameOfView2 varchar(32);  

DECLARE var3           smallint;

 

 

DECLARE my_cur CURSOR FOR

select APView_Name,'''' || trim( APView_Name) ||'''', row_number() OVER (order by APView_id) as id

from dw_prod.F_DL_APviews_zu ;

 

 

open my_cur;

 

Label1:LOOP

fetch my_cur into varNameOfView, varNameOfView2, var3 ;

CALL DBC.SysExecSQL (

        'UPDATE a 

        FROM dw_prod.F_DL_APviews_zu a,

        (SELECT max(T_Batch_Last_id) as T_Batch_Last_id from ap_prod.'||:varNameOfView ||') fv, 

        dw_prod.L_DL_Batch_zu l

         SET T_Batch_Last_id=fv.T_Batch_Last_id

        where fv.T_Batch_Last_id=l.Batch_id and l.ActiveFlag_id=1 and a.APView_Name='||:varNameOfView2 ||';'  

); 

  IF ( SQLSTATE = ' 02000' ) THEN LEAVE Label1 ; 

  END IF ;

END LOOP Label1 ;

 

 

close my_cur;

 

END;

You must sign in to leave a comment.