Solved

Commit rollback inside cursor

Posted on 2002-04-20
3
1,394 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Google Drive is extremely cheap offsite storage, and it's even possible to get extra storage for free for two years.  You can use the free account 15GB, and if you have an Android device..when you install Google Drive for the first time it will give…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

813 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

11 Experts available now in Live!

Get 1:1 Help Now