Hi Everybody,
My Question is that I am declaring a Cursor WHICH NORMALLY FETCH SOME DATA FROM THE CURSOR AND THEN FOR PARTICULAR COLUMN I MAKE SOME CALCULATION FOR PARTICULAR COLUMN AND THEN UPDATE SAME ROWWITH THAT UPDATED VALUE FOR THIS COLUMN..
AND SINCE I WAS DOING SAME THING WITHOUT USING WHERE CURRENT OF AND FOR UPDATE CLAUSE BY SIMPLE UPDATE QUERY AFTER FETCHING THE RECORD...BUT AS PER OPTIMIZATIONTHI S WILL TAKE MORE TIME....SO PLEASE HAVE A LOOK INTO MY ISSUE..AND PLEASE GIVE A SOLUTION...
BELOW ARE THE CURSOR DEFINITION WHICH I AM USING TO UPDATE IT..
EXEC-SQL
Declare CSR2 CURSOR WITH HOLD FOR
SELECT A.COLUMNS,
A.COLUMNB,
A.COLUMNC,
B.COLUMNA,
B.COLUMNB,
C.COLUMNA,
D.COLUMNB
FROM
PR_PROMO A
PR_PROMO_OFFR B
PR_PROMO_ACCT C
ELGBLE_RATE D
WHERE
SOME CONDITION.....
FOR UPDATE OF COLUMNS
OPEN....CURSOR STATEMENT...
THEN
FETCH CURSOR STATEMENT,...
THEN UPDATE ..
UPDATE PR_PROMO
SET COLUMNS = :COLUMNS
WHERE CURRENT OF CSR2
EVALUTE SQLCODE
WHEN 0
EXEC-SQL
COMMIT
END-EXEC
WHEN OTHER
EXEC-SQL
ROLLBACK
END-EXEC
THEN CLOSE CURSOR...
BUT WHEN I AM BINDING I AM FACING SQL-CODE = -510..
THAT COMES WHEN WE FETCH THE CURSOR IN READ ONLY MODE AND TRY TO UPDATE ANY RECORD.
My Question is that I am declaring a Cursor WHICH NORMALLY FETCH SOME DATA FROM THE CURSOR AND THEN FOR PARTICULAR COLUMN I MAKE SOME CALCULATION FOR PARTICULAR COLUMN AND THEN UPDATE SAME ROWWITH THAT UPDATED VALUE FOR THIS COLUMN..
AND SINCE I WAS DOING SAME THING WITHOUT USING WHERE CURRENT OF AND FOR UPDATE CLAUSE BY SIMPLE UPDATE QUERY AFTER FETCHING THE RECORD...BUT AS PER OPTIMIZATIONTHI S WILL TAKE MORE TIME....SO PLEASE HAVE A LOOK INTO MY ISSUE..AND PLEASE GIVE A SOLUTION...
BELOW ARE THE CURSOR DEFINITION WHICH I AM USING TO UPDATE IT..
EXEC-SQL
Declare CSR2 CURSOR WITH HOLD FOR
SELECT A.COLUMNS,
A.COLUMNB,
A.COLUMNC,
B.COLUMNA,
B.COLUMNB,
C.COLUMNA,
D.COLUMNB
FROM
PR_PROMO A
PR_PROMO_OFFR B
PR_PROMO_ACCT C
ELGBLE_RATE D
WHERE
SOME CONDITION.....
FOR UPDATE OF COLUMNS
OPEN....CURSOR STATEMENT...
THEN
FETCH CURSOR STATEMENT,...
THEN UPDATE ..
UPDATE PR_PROMO
SET COLUMNS = :COLUMNS
WHERE CURRENT OF CSR2
EVALUTE SQLCODE
WHEN 0
EXEC-SQL
COMMIT
END-EXEC
WHEN OTHER
EXEC-SQL
ROLLBACK
END-EXEC
THEN CLOSE CURSOR...
BUT WHEN I AM BINDING I AM FACING SQL-CODE = -510..
THAT COMES WHEN WE FETCH THE CURSOR IN READ ONLY MODE AND TRY TO UPDATE ANY RECORD.
Comment