Solved

Commit rollback inside cursor

Posted on 2002-04-20
3
1,391 Views
Last Modified: 2012-06-27
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
Comment
Question by:grao5
  • 2
3 Comments
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6960011

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
 

Author Comment

by:grao5
ID: 6960977
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
 
LVL 5

Accepted Solution

by:
amitpagarwal earned 50 total points
ID: 6960983
Thats great.
you may please close this question.

thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
With the rapid rise in mobile usage, mobile devices are here to stay and have become an integral part of doing business. Here are 9 great apps for your BYOD environment.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now