Trying to find the syntax DELETE WHERE CURRENT, did not find any info. Ultimately, I am READING 2 tables via inner join,get error 100 , so I do not understand how it got fetched the second time. Mu guess its has to do with buffering .
and then update the company # on the record. Since company# is a key, so will have to delete and reinsert with new company number.
I have few question
1- How would you declare the cursor? is it different than READ only cursor? I am getting error 8223 when perform the delete. Currently it's as below: does this looks correct?
EXEC SQL
DECLARE GET_OPEN_ITEM CURSOR FOR
SELECT A.COLUMN1
, B.COLUMN1
FROM =TABLE1 A, TABLE2 B
INNER JOIN TABLE1 ON TABLE2
WHERE A.COLUMN1 = B.COLUMN2
BROWSE ACCESS
END-EXEC
2- When I perform a record DELETE, I will need to write 2 different DELETE statements, once for each table, correct?
3- I tried to do a DELETE and INSERT without using DELETE WHERE CURRENT, just regular delete, it worked. But when I fetch I still get the same record I just delete and inserted with new company #. The thing is when the process try to delete again, I
Any idea or thoughts will be highly appreciated.
Trying to find the syntax DELETE WHERE CURRENT, did not find any info. >Ultimately, I am READING 2 tables via inner join,get error 100 , so I do not understand how it got fetched the second time. Mu guess its has to do with buffering .
and then update the company # on the record. Since company# is a key, so will have to delete and reinsert with new company number.
I have few question
1- How would you declare the cursor? is it different than READ only cursor? I am getting error 8223 when perform the delete. Currently it's as below: does this looks correct?
EXEC SQL
DECLARE GET_OPEN_ITEM CURSOR FOR
SELECT A.COLUMN1
, B.COLUMN1
FROM =TABLE1 A, TABLE2 B
INNER JOIN TABLE1 ON TABLE2
WHERE A.COLUMN1 = B.COLUMN2
BROWSE ACCESS
END-EXEC
2- When I perform a record DELETE, I will need to write 2 different DELETE statements, once for each table, correct?
3- I tried to do a DELETE and INSERT without using DELETE WHERE CURRENT, just regular delete, it worked. But when I fetch I still get the same record I just delete and inserted with new company #. The thing is when the process try to delete again, I
Any idea or thoughts will be highly appreciated.
On Tuesday, December 14, 2021 at 1:05:30 p.m. UTC-5, abol...@gmail.com wrote:get error 100 , so I do not understand how it got fetched the second time. Mu guess its has to do with buffering .
Trying to find the syntax DELETE WHERE CURRENT, did not find any info. Ultimately, I am READING 2 tables via inner join,
and then update the company # on the record. Since company# is a key, so will have to delete and reinsert with new company number.
I have few question
1- How would you declare the cursor? is it different than READ only cursor? I am getting error 8223 when perform the delete. Currently it's as below: does this looks correct?
EXEC SQL
DECLARE GET_OPEN_ITEM CURSOR FOR
SELECT A.COLUMN1
, B.COLUMN1
FROM =TABLE1 A, TABLE2 B
INNER JOIN TABLE1 ON TABLE2
WHERE A.COLUMN1 = B.COLUMN2
BROWSE ACCESS
END-EXEC
2- When I perform a record DELETE, I will need to write 2 different DELETE statements, once for each table, correct?
3- I tried to do a DELETE and INSERT without using DELETE WHERE CURRENT, just regular delete, it worked. But when I fetch I still get the same record I just delete and inserted with new company #. The thing is when the process try to delete again, I
Any idea or thoughts will be highly appreciated.It appears that your issue is that you are using BROWSE ACCESS. If you are doing transaction processing, use either default or EXCLUSIVE access. Then you should not get the record fetched after deleted.
On Tuesday, December 14, 2021 at 11:15:27 AM UTC-8, Randall wrote:I get error 100 , so I do not understand how it got fetched the second time. Mu guess its has to do with buffering .
On Tuesday, December 14, 2021 at 1:05:30 p.m. UTC-5, abol...@gmail.com wrote:
Trying to find the syntax DELETE WHERE CURRENT, did not find any info. Ultimately, I am READING 2 tables via inner join,
and then update the company # on the record. Since company# is a key, so will have to delete and reinsert with new company number.
I have few question
1- How would you declare the cursor? is it different than READ only cursor? I am getting error 8223 when perform the delete. Currently it's as below: does this looks correct?
EXEC SQL
DECLARE GET_OPEN_ITEM CURSOR FOR
SELECT A.COLUMN1
, B.COLUMN1
FROM =TABLE1 A, TABLE2 B
INNER JOIN TABLE1 ON TABLE2
WHERE A.COLUMN1 = B.COLUMN2
BROWSE ACCESS
END-EXEC
2- When I perform a record DELETE, I will need to write 2 different DELETE statements, once for each table, correct?
3- I tried to do a DELETE and INSERT without using DELETE WHERE CURRENT, just regular delete, it worked. But when I fetch I still get the same record I just delete and inserted with new company #. The thing is when the process try to delete again,
Hello Randall,Any idea or thoughts will be highly appreciated.It appears that your issue is that you are using BROWSE ACCESS. If you are doing transaction processing, use either default or EXCLUSIVE access. Then you should not get the record fetched after deleted.
Changed the cursor BROWSE ACCESS to REPRATABLE ACCESS, but it does not seem to work. Program continues to pickup already processed record. I guess the buffer where the data is stored on OPEN cursor is changing when I delete/insert the record.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 286 |
Nodes: | 16 (2 / 14) |
Uptime: | 88:12:48 |
Calls: | 6,496 |
Calls today: | 7 |
Files: | 12,100 |
Messages: | 5,277,325 |