Instead of Cursors, give me simple way to do

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Instead of Cursors, give me simple way to do

Author

Topic

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-23 : 01:28:45

Table Structures
Classid Invtid Seqnbr
000001 000045 0
000001 000048 0
000001 000049 1
000001 000051 3
000001 000052 2
000002 000053 0
000002 000055 1
..
..
000002 000059 4
...
...

Primary key in this table is Invtid. We have to leave the seqnbr which has Zero's, appart from that
for repeated classid the seqnbr have to change in order.
For eg:-

000001 000045 0
000001 000048 0
000001 000049 1
000001 000051 2
000001 000052 3

The above result is for one group of classid, like that for next group we have to leave
the seqnbr which has 0 and the other seqnbr number have to be in order start from 1...n

I have used Cursors to solve the problem and done that.

My SP is

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE id = object_id('dbo.xttsinvent') and sysstat & 0xf = 4)
DROP PROCEDURE xttsinvent
GO
CREATE PROCEDURE xttsinvent
AS
DECLARE @Seqnbr int,@ClassID varchar(10)
DECLARE @InvIDTmp varchar(30)
--SET @SeqNbr = 1
DECLARE xttsCurr_Outer CURSOR
FOR SELECT distinct ClassID FROM xttsinventorytmp WHERE seqnbr <> 0 ORDER BY ClassID
OPEN xttsCurr
FETCH NEXT FROM xttsCurr_Outer INTO @ClassID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SeqNbr = 1
DECLARE xttsCurr_Inner CURSOR
FOR SELECT InvtID FROM xttsinventorytmp WHERE SeqNbr <> 0 and classid = @Classid ORDER BY SeqNbr
OPEN xttsCurr_Inner
FETCH NEXT FROM xttsCurr_Inner INTO @InvIDTmp
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE xtts
SET xtts.SeqNbr = @SeqNbr
FROM xttsinventorytmp xtts WHERE xtts.Invtid = @InvIDTmp
SET @SeqNbr = @SeqNbr + 1
FETCH NEXT FROM xttsCurr_Inner INTO @InvIDTmp
END
CLOSE xttsCurr_Inner
DEALLOCATE xttsCurr_Inner
FETCH NEXT FROM xttscurr_Outer INTO @ClassID
END
CLOSE xttscurr_Outer
DEALLOCATE xttscurr_Outer
GO

Can any one tell me is there another method to do without using Cursors.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-23 : 02:19:41

Did you get solution from here?
http://sqlteam.com/forums/topic.asp?TOPIC_ID=68059

Madhivanan

Failing to plan is Planning to fail

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-23 : 02:28:58

maddy in that update stmt id I use classid as distinct means will that work?.

Thats what you would like to explain. Is it so?.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-23 : 02:50:25

Do U Need the Distinct Sequence No for InvtID..?

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-23 : 02:52:50

First Take a backupfor that table then try this query and let be back

Declare @SeqNbr int
Select @SeqNbr = 1

UPDATE xtts
SET xtts.SeqNbr = @SeqNbr ,@SeqNbr = @SeqNbr + 1
FROM xttsinventorytmp xtts WHERE xtts.SeqNbr <>0

-- KK

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-23 : 04:09:56

No distinct number for invtid.

suppose the below is the case means
ClassID InvtID SeqNbr
000001 000045 0
000001 000048 0
000001 000049 1
000001 000051 3
000001 000052 2
000002 000053 0
000002 000055 1
000002 000056 4

The result has to be in form
000001 000045 0
000001 000048 0
000001 000049 1
000001 000051 2
000001 000052 3
000002 000053 0
000002 000055 1
000002 000056 2
..
..

like wise for each classid.

I think now u will get me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-23 : 05:11:15

[code]declare @test table (ClassID varchar(30), InvtID varchar(10), SeqNbr int)
insert @test
select '000001', '000045', 0 union all
select '000001', '000048', 0 union all
select '000001', '000049', 1 union all
select '000001', '000051', 3 union all
select '000001', '000052', 2 union all
select '000002', '000053', 0 union all
select '000002', '000055', 1 union all
select '000002', '000056', 4

update t
set SeqNbr = (select count(*) from @test x where x.ClassID = t.ClassID and x.SeqNbr <> 0 and x.InvtID <= t.InvtID)
from @test t
where t.SeqNbr <> 0

select * from @test

/* RESULT :
ClassID InvtID SeqNbr
------------------------------ ---------- -----------
000001 000045 0
000001 000048 0
000001 000049 1
000001 000051 2
000001 000052 3
000002 000053 0
000002 000055 1
000002 000056 2
*/[/code]

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-23 : 05:36:39

Thanks khtan,

This is the second time you came up an nice solution for me.
In my previous topic I asked you to explain the logic of the program, Ihave understood bit but not clear.

If you don't please explain this programming logic of how it works.

What is happenning inside the bracket i.e, (select count(*)..)
Will the program check the everyline to update or it will update the entire in fetch.

Hope you will help me in this...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-23 : 05:45:13

Sure. Here it is

update	t
	set	SeqNbr	= (select count(*) from @test x  -- Count the number of records 
                            where x.ClassID = t.ClassID  -- for each group of ClassID
                            and   x.SeqNbr  <> 0         -- ignoring those with SeqNbr = 0
                            and   x.InvtID  <= t.InvtID) -- for InvtID less or equal
from	@test t
where	t.SeqNbr	<> 0     -- your criteria of ignore those with SeqNbr = 0

/* RESULT :
ClassID                        InvtID     SeqNbr      
------------------------------ ---------- ----------- 
000001                         000045     0           ignored
000001                         000048     0           ignored
000001                         000049     1           counted 1 record  (000049)
000001                         000051     2           counted 2 records (000049 & 000051)
000001                         000052     3           counted 3 records (000049 , 000051 & 000052)
000002                         000053     0           ignored
000002                         000055     1           counted 1 record  (000055)
000002                         000056     2           counted 2 records (000055 & 000056)
*/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-23 : 05:56:08

The reason of why I want you to do this in front end application is that for each row count is calculated. If you run that query against a table having millions of rows it may take hours to number them

Madhivanan

Failing to plan is Planning to fail

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-23 : 06:07:53

Thanks khtan, no need to explain the logic I have understood by reading once again and what there in logic.

Maddy you are right, but I cannot do that front end application. B'coz for entering values in front end app means what you are telling will definetly get solution. But the record was already created and if we enter there might some duplicates and we cannot know what exactly is going. so that I created sp to check and change the value.

Thanks for all especially khtan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources