By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,616 Members | 1,184 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,616 IT Pros & Developers. It's quick & easy.

Can we use For Update Of Clause with Cursor where we are using for 4 tables?

P: 2
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 OPTIMIZATIONTHIS 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.
Dec 10 '10 #1
Share this Question
Share on Google+
1 Reply


P: 2
Please reply fast..
Dec 10 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.