How to control the UPDATE in Cursor?

babu_hi · New User Joined: 11 Apr 2006 Posts: 93

I have two cursors in my program and updating the one table.

The Table structure is like below

TBE_NME TBE_DTE FLAG
ARW_X9_T001 000000 H
ARW_X9_T002 000000 H
ARW_X9_T003 000000 H
ARW_X9_T004 000000 A

I decalared Cursor1 like this and fetching only FLAG=A records from the table.

EXEC SQL DECLARE CNTL_TABLE_CSR1 CURSOR WITH HOLD FOR
SELECT TBE_NME,
TBE_DTE,
CFLAG
FROM ARW_CNTL_TABLE
WHERE FLAG = 'A'
FOR UPDATE OF FLAG
END-EXEC.

Then i am update the Flag value with H
Move 'H' to WS-FLAG.

EXEC SQL
UPDATE ARW_CNTL_TABLE
SET FLAG = :WS-FLAG
WHERE CURRENT OF CNTL_TABLE_CSR1
END-EXEC.

Now i am closeing the cursor.

And the second cursor i declared like below and retriving only FLAH = H records,

EXEC SQL DECLARE CNTL_TABLE_CSR2 CURSOR WITH HOLD FOR
SELECT TBE_NME,
TBE_DTE,
FLAG
FROM ARW_CNTL_TABLE
WHERE CNTL_FLAG = 'H'
FOR UPDATE OF FLAG
END-EXEC.

Then i update the flag value with A
Move 'A' to WS-FLAG.

EXEC SQL
UPDATE ARW_CNTL_TABLE
SET FLAG = :WS-FLAG
WHERE CURRENT OF CNTL_TABLE_CSR2
END-EXEC.

But i don't want to update the FLAG vale changed from 'A' to 'H' at the first cursor,i need only update the remaing records form the table.

The OUTPUT of the Table should be like this

TBE_NME TBE_DTE FLAG
ARW_X9_T001 000000 A
ARW_X9_T002 000000 A
ARW_X9_T003 000000 A
ARW_X9_T004 000000 H

But if i execute the program i am getting the OUTPUT like this

TBE_NME TBE_DTE FLAG
ARW_X9_T001 000000 A
ARW_X9_T002 000000 A
ARW_X9_T003 000000 A
ARW_X9_T004 000000 A

Please let me know how to control the second cursor UPDATE?

shrivatsa · Posted: Wed Apr 09, 2008 11:40 am

If you have any other primary keys then you can control otherwise its better to Update first A to X and then H to A and again X to H.

enrico-sorichetti · Posted: Wed Apr 09, 2008 11:51 am

use only one cursor with ...
WHERE FLAG = 'A' or FLAG = 'H'

select also the flag after the fetch check for

if old-flag = " A"
ws_flag = value 1
else if old-flag = "H"
ws-flag = value 2

update blah bllah

I do not speak cobolese, yust translate to the proper cobolese constructs

babu_hi · New User Joined: 11 Apr 2006 Posts: 93

as per my requirement i need to use two cursors....please give a solution with more scudeo code?

dick scherrer · Posted: Wed Apr 09, 2008 12:04 pm

Hello,

What business requirement dictates that 2 cursors must be used?

It is not a good idea to defend an implementation that is flawed when a solution that meets the business requirement is presented.

Why would you not simplify the code and implement as Enrico has suggested?

enrico-sorichetti · Posted: Wed Apr 09, 2008 12:17 pm

You cannot do it in a simple way with 2 cursors, take it or leave it
or even if it is possible it is not worth the time it takes to think about it

Your choices are ...
1 as I suggested or
3 as shrivatsa suggested
to make things clear to everybody reading the program

unless You want to play around with isolation levels,
but it would take time to test and analyze the consequences and drawbacks
in this case the program behavior would rely on somebody binding the plan/package with the proper isolation level

it is hard to believe that the use of exactly two cursor is an application design requirement
unless this is some kind of exam

sri_mf · Active User Joined: 31 Aug 2006 Posts: 218 Location: India

babu_hi · New User Joined: 11 Apr 2006 Posts: 93

Thanks all of you....as you people suggested i will implement the my requirement with one cursor...if i have any queries i will post with full information.