• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1433
  • Last Modified:

Commit rollback inside cursor

Hello experts,
             I have written a Sybase 11.9 (Adaptive Server) cursor which is basically  supposed to support the following functionality:
(1) I am migrating a table from an old schema to a new schema. I take the old schema table, select a
group of records having a particular value in a column 'X' , check if the number of records in that
group is 12, and if so I delete any 11 of those 12 and retain just 1 record. I repeat this for all distinct
values in column 'X'
Here is the algorithm for how the cursor implements above functionality:

1)Sort table by column X and copy it to temp table
2)Use the temptable as input to the cursor
3)For every record returned by cursor, retain it if it is the first record with a particular value (eg:'ABCD' )
in column X and do BEGIN TRAN
4)If it is not the first record with the above value 'ABCD' in column X I delete it. I keep repeating
this delete till the value in column X changes.
5) Then I check the counter to see if it = 12. If so, I COMMIT the transaction. If not, I rollback the
transaction so that the deletes for 'ABCD' are rolledback.
6) I do this for every distinct value in column X.


Problem: The cursor is not returning all the rows in the temptable. So my process is working only on
part of the table. For some reason I suspect the cursor is getting closed in the middle or something
else is happening.
I am using rollbacks because I do not know before hand if for a given value in column X, there are
12 records or (more/less). If 12 I commit the deletes else I rollback all previous deletes for that
value of column X.

I would appreciate if you could provide a solution to the problem.

thanks
grao5
0
grao5
Asked:
grao5
  • 2
1 Solution
 
amitpagarwalCommented:

select pri_key_col1, pri_key_col2 ...
from tableName
where some_col = "X"
group by pri_key_col1, pri_key_col2 ..
having count(*) = 12


as far as i understand ..the above query is all you need

could you please post more details , like table definition, some sample data .. it will help us easily answer ur quest.
0
 
grao5Author Commented:
Hi,
  Thanks for the suggestion. Actually I found an alternate solution where I have completely eliminated cursors, because cursors have the property of closing upon use of commit/rollback transaction. I tried to eliminate this problem using chained transaction mode & hold locks  when declaring the cursor. But it did not work.
So now I eliminated cursors completely, and instead do lot of intermediate processing using temporary tables, while loops, changing rowcounts and other transact sql stuff and I think I am able to achieve the desired results. If I experience any additional problems I will get in touch.

thanks
grao5
0
 
amitpagarwalCommented:
Thats great.
you may please close this question.

thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now