IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this community and its apps will no longer be available. More details available on our FAQ.
Topic
  • 1 reply
  • Latest Post - ‏2019-03-01T03:23:45Z by stiruvee
robberendt
robberendt
84 Posts

Pinned topic SPL question about error handling.

‏2019-02-28T23:23:28Z |

Newbie to SPL.

Really trying to get the basics of error handling.

 

Basically I have a loop

Declare c1 cursor for select …

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET ROW_NOT_FOUND = 1;

Open c1;

Fetch c1 into fetchColumn1, fetchColumn2…

SET END_CURSOR = ROW_NOT_FOUND;

WHILE END_CURSOR = 0 DO

Insert into AnotherTable (

Select fetchColumn1, fetchColumn2, (select * from YetAnotherTable));

Note: rows are often not found in YetAnotherTable. In that case I just want to skip this insert and fetch the next row from the cursor.

Set row_not_found = 0;

Fetch c1 into fetchColumn1, fetchColumn2…

Set END_CURSOR = row_not_found;

End while;

Close C1;

End p1;

 

The problem is that when it hits the missing row in YetAnotherTable, it sets SQLCODE, SQLSTATE, ROW_NOT_FOUND, exits the loop and the procedure.

  • stiruvee
    stiruvee
    48 Posts

    Re: SPL question about error handling.

    ‏2019-03-01T03:23:45Z  

    <: rows are often not found in YetAnotherTable. In that case I just want to skip this insert and fetch the next row from the cursor. >

     

    Change insert SQL to

     

    Insert into AnotherTable
    Select fetchColumn1, fetchColumn2,  YetAnotherTable.*
    from YetAnotherTable;