where current of cursorName 1

[jaylou]

Hi all,
I am trying to sequence rows in a table. this is for different policy#'s
I am using policy#,effectiveDate as my critiria to start my numbering. The
problem is there are no unique Identifiers in this table I have 6 rows that
are exact dupes. when I run a normal cursor, it updates all 6 with the same
seqNo( I understand why).
I can't get the statement where current of cursorName to work. I get an
error saying:
"Msg 16929, Level 16, State 1, Line 35
The cursor is READ ONLY.
The statement has been terminated."

I need to be able to update ONLY the row in the cursor...

I am including my code below..................Yes TransTime can also be a
dupe.

Any help would be greatly appreciated.
Joe
/******************************/
declare @seqid int, @PolicyNo nvarchar(10), @EffDate datetime,
@TransactionDate datetime, @TranactionTime float,
@KeyHold nvarchar(255),@Key nvarchar(255)

select @seqid=0
Select @keyHold = ' '
Select @key = ' '

declare SEQID cursor for
select policyNo,EffDate,Transactiondate,TranactionTime
from tblpolicyTest
order by policyNo,EffDate,Transactiondate,TranactionTime

open seqid

fetch next from seqid into
@PolicyNo,@EffDate,@TransactionDate,@TranactionTime

while @@fetch_status=0
begin

Select @key = rtrim(isnull(@PolicyNo,' '))+rtrim(isnull(@EffDate,' '))

if @key <> @keyHold
begin
select @seqid =1
update tblpolicyTest
set SeqNo = @seqid
where current of seqid
-- Where PolicyNo = @PolicyNo
-- And EffDate = @EffDate
-- And TransactionDate = @TransactionDate
-- And TranactionTime = @TranactionTime
end
else
begin
select @seqid =@seqid+1
update tblpolicyTest
set SeqNo = @seqid
where current of seqid
-- Where PolicyNo = @PolicyNo
-- And EffDate = @EffDate
-- And TransactionDate = @TransactionDate
-- And TranactionTime = @TranactionTime
end

Select @keyHold = rtrim(isnull(@PolicyNo,' '))+rtrim(isnull(@EffDate,' '))

fetch next from seqid into
@PolicyNo,@EffDate,@TransactionDate,@TranactionTime

end

close seqid
deallocate Seqid
/***************************************/

 

[vongrunt]

Wanna use WHERE CURRENT OF, ya must declare cursor FOR UPDATE.

Btw. if this is temporary routine, it can be done without cursors.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Burglar]

Instead of

declare SEQID cursor for
select policyNo,EffDate,Transactiondate,TranactionTime
from tblpolicyTest
order by policyNo,EffDate,Transactiondate,TranactionTime


try:

declare SEQID cursor for
select policyNo,EffDate,Transactiondate,TranactionTime
from tblpolicyTest
order by policyNo,EffDate,Transactiondate,TranactionTime
for update

 

[jaylou]

Burglar,
I get this error when I tried your change:
Server: Msg 16957, Level 16, State 4, Line 18
FOR UPDATE cannot be specified on a READ ONLY cursor.


Vongrunt,
If you can give me an idea how to avoid a cursor, I would be very interested!

Thanks to both

 

[vongrunt]

So... policyNo and EffDate are grouping columns, while TransactionDate/Time dictate order inside group. Unfortunately all these columns together arent' unique. Is there any other column that can provide uniqueness?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jaylou]

Ah and there lies the problem... this is coming from an AS400 system where the way they update a record is to enter a reversing entry then inserting the new value. If someone pays $100 and the data entry clerk places $50, they must add another row of $50. the 2 rows will be exactly the same if they enter the second on the same day. Dont know why the time also stays the same for this type of situation, but it does. If I had unique rows I would have no problem doing this with or without a cursor.

That is the reason I am looking for current of cursor. this way it will only update the row in the cursor.

Thanks again,
Joe

 

[vongrunt]

Can you temporarily add identity column?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jaylou]

Vongrunt,
I have added an Identity column. I can now update thru my cursor. The only issue is the speed... There are over 5,000,000 rows in my table and it is taking forever to finish.
You mentioned an alternative to cursors, Can you please give me an example of how to add this sequence number without a cursor?

Thanks,
Joe

 

[vongrunt]

There are some 3-4 possible ways without cursors. Here is what I had in mind:

-- perform ORDERed insert into temp table. 
-- Insert unique key, running identity, final sequence and grouping columns
-- idcol is identity column you previously created
select idcol as uniqueID, identity(int, 1, 1) as insertseq, convert(int, null) as finalseq, policyNo, EffDate
into #blah
from tblPolicyTest
order by policyNo,EffDate,Transactiondate,TransactionTime
option (maxdop 1)

-- that should make things faster
alter table #blah add primary key clustered ( insertseq )

-- for each row, calculate sequence as running identity - (min. value in a group - 1)
update A
set finalseq = A.insertseq - B.minseq
from #blah A
inner join 
(	select policyNo, EffDate, min(insertseq)-1 as minseq
	from #blah
	group by policyNo, EffDate
) B
on A.policyNo=B.policyNo and A.EffDate=B.EffDate

-- join back with original table on unique key (identity column!)
update T
set SeqNo = B.finalseq
from tblPolicyTest T
inner join #blah B on T.idcol = B.uniqueID

drop table #blah

The only potential pitfall here is ORDERed insert. Wanna try it and post results back, I'd appreciate that.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jaylou]

Thank you!!!!! This worked great!!
it took about an hour while my developers were in the DB working and doing their jobs.

The only correction I needed to do was to create the #blah table before inserting into it. it did not like the fact of adding a second identity column. my orig ID column property came accross to he new temp table.

Thank you again!!!!!!!!!
Joe

 

[vongrunt]

And generated sequence is absolutely OK?

I was a little bit worried about that. There were some reports that INSERT with ORDER BY does not guarantee insertion order as specified with ORDER BY - and some others claiming that problem went bye-bye with parallelism turned off (MAXDOP=1).

5.5M rows for an hour is not warp 1 speed but... job get done = all that matters .

Anyway, thank you for feedback.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jaylou]

the speed issue was definatly due to my developers hitting the table at the same I was running my update. when I updated the temp table it took less than 5 minutes.

I am still in the middle of checking everything, so far every policy I am checking is sequenced the way I intended.

Thanks again,
Joe