I have a AS400 table that contains multiple rows per person. Each row contains data concerning a transaction by the person including points assigned to each transaction. I read through all rows for one person ordered by the points. The first row with the highest points, I leave alone. On subsequent rows the point values are cut in half and I do an update. I am currently using a cursor to open the table and doing a Loop to read through the rows. I am getting an
-508 error stating :
An UPDATE or DELETE statement with a WHERE CURRENT OF CL was attempted, but the cursor is not positioned on a row or is positioned on a row, but the row is not locked because a COMMIT HOLD or ROLLBACK HOLD statement released the lock on the row. A FETCH statement must be issued to position the cursor on a row and lock the row. Recovery . . . : Issue a FETCH statement to position the cursor on a row and lock the row; then, try the request again.
Part of my code is below:
DECLARE V_LNAME CHAR ( 30 ) ; DECLARE V_LNAMEHOLD CHAR ( 30 ) ; DECLARE V_FNAME CHAR ( 15 ) ; DECLARE V_FNAMEHOLD CHAR ( 15 ) ; DECLARE V_DOB DATE ; DECLARE V_DOBHOLD DATE ; DECLARE V_TRANSNMBR CHAR ( 9 ) ; DECLARE V_TRANSNMBRHOLD CHAR ( 9 ) ; DECLARE V_POINTS NUMERIC ( 5 ) ; DECLARE V_POINTSHOLD NUMERIC ( 5 ) ; DECLARE V_POINTSEQ NUMERIC ( 5 ) ; DECLARE FIRSTRECORD CHAR ( 1 ) ; DECLARE CL CURSOR FOR SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS FROM DB_TRANSDATA ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ; DECLARE CLHLD CURSOR FOR SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS FROM DB_TRANSDATA ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ; OPEN CLHLD ; FETCH CLHLD INTO V_LNAMEHOLD , V_FNAMEHOLD , V_DOBHOLD , V_TRANSNMBRHOLD ; close clhld; OPEN CL ; SET FIRSTRECORD = 'Y' ; LOOP FETCH CL INTO V_LNAME , V_FNAME , V_DOB , V_TRANSNMBR , V_POINTS , V_POINTSEQ ; IF TRIM ( CHAR ( V_LNAME ) ) = TRIM ( CHAR ( V_LNAMEHOLD ) ) AND TRIM ( CHAR ( V_FNAME ) ) = TRIM ( CHAR ( V_FNAMEHOLD ) ) AND V_DOB = V_DOBHOLD AND V_TRANSNMBR = V_TRANSNMBRHOLD AND FIRSTRECORD = 'N' THEN SET V_POINTSEQ = V_POINTS * .5 ; UPDATE DB_TRANSDATA SET POINTSEQ = V_POINTSEQ WHERE CURRENT OF CL ; SET V_LNAMEHOLD = V_LNAME ; SET V_FNAMEHOLD = V_FNAME ; SET V_DOBHOLD = V_DOB ; SET V_TRANSNMBRHOLD = V_TRANSNMBR ; ELSE UPDATE DB_TRANSDATA SET POINTSEQ = V_POINTS WHERE CURRENT OF CL ; SET V_LNAMEHOLD = V_LNAME ; SET V_FNAMEHOLD = V_FNAME ; SET V_DOBHOLD = V_DOB ; SET V_TRANSNMBRHOLD = V_TRANSNMBR ; SET FIRSTRECORD = 'N' ; END IF ; END LOOP ; CLOSE CL; END ;