cursor workaround? - SQL Server Forums

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)

cursor workaround?

Author

Topic

simbeeosis
Starting Member

10 Posts

Posted - 2005-08-08 : 06:54:03

hi all,

i'm trying to find a workaround for a script i wrote that uses cursor in sql server 2000 (cursor is a bit slow).
i have a table full of transactions some of which have the same tid. the rows with more that one tid represent the same transaction but allocated to diferent categories.
i need to extract all transactions with only one tid and also the first row for transactions with multiple splits.

eg;

tid,name, category
1224, smith, cat1
1224, smith, cat2
1223, jones, cat4

given the table above, i need the following results
1224, smith, cat1
1223, jones, cat4

hope thats clear. here is the script i wrote. it does work but its little slow.

DECLARE @mytid int
DECLARE @mycat nvarchar (50)
DECLARE @myemail nvarchar (50)
DECLARE @myamount float
DECLARE @mygfdate smalldatetime
DECLARE @pkey int
DECLARE @mytidcompare int
DECLARE @mycatcompare nvarchar (50)
DECLARE @myemailcompare nvarchar (50)
DECLARE @myamountcompare float
DECLARE @mygfdatecompare smalldatetime
DECLARE @pkeycompare int
--DEALLOCATE mycursor
DECLARE mycursor CURSOR dynamic FOR SELECT tid, category, email, amount, gfdate, pkey
FROM [gift aid].[dbo].splitgiftlist

OPEN mycursor

FETCH first
from mycursor
INTO @mytid, @mycat, @myemail, @myamount, @mygfdate, @pkey
INSERT
INTO [gift aid].dbo.splitsinglefirst(tid, category, email, amount, gfdate, pkey)
VALUES (@mytid, @mycat, @myemail, @myamount, @mygfdate, @pkey)

print 'tid first'
print @mytid
WHILE @@fetch_status = 0
BEGIN

FETCH myCURSOR
INTO @mytid,@mycat, @myemail, @myamount, @mygfdate, @pkey
print '@mycat second'
print @mycat
--FETCH PRIOR
--FROM mycursor
--INTO @mytidcompare,@mycatcompare, @myemailcompare, @myamountcompare, @mygfdatecompare
print '@mycat compare'
print @mycat
--print 'compare'
--print @mytidcompare
print 'tid 2nd'
print @mytid
--if @mytidcompare <> @mytid

IF @mytid not in (select tid from [gift aid].dbo.splitsinglefirst)

begin
INSERT
INTO [gift aid].dbo.splitsinglefirst(tid, category, email, amount, gfdate, pkey)
VALUES (@mytid, @mycat, @myemail, @myamount, @mygfdate, @pkey)

end
--fetch next from mycursor
END
CLOSE mycursor
DEALLOCATE mycursor

ps: please bear in mind this is my first sql script.

regards
s

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-08 : 07:05:27

See if this works

select tid,name, category from yourtable
where category in (select max(category) from yourtable group by tid)

Madhivanan

Failing to plan is Planning to fail

simbeeosis
Starting Member

10 Posts

Posted - 2005-08-08 : 07:13:57

this returns all rows in the source table. the subquery isolates the records i need but won't give me the columns that i need.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-08 : 07:21:16

Then you need to give more sample data
Post some 20 sample data

Madhivanan

Failing to plan is Planning to fail

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-08 : 07:26:06

try this

select IDENTITY(int,1,1) as abc, tid,name, category, email, amount, gfdate, pkey into #temp from splitgiftlist

select tid,name, category, email, amount, gfdate, pkey from #temp t2
where abc in (select min(t1.abc) from #temp t1 group by t1.tid and t1.tid = t2.tid)

Kapil Arya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-08 : 07:38:49

If category is not duplicated for tid, then this should give the correct result

select tid,name,max(category) from yourtable group by tid,name

Madhivanan

Failing to plan is Planning to fail

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-08 : 07:41:56

Madhivanan he wants the first record of the same tid and not the max category

tid,name, category
1224, smith, cat1
1224, smith, cat2
1223, jones, cat4

given the table above, i need the following results
1224, smith, cat1
1223, jones, cat4

Kapil Arya

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-08-08 : 08:26:03

There is no "first record". there has to be another defining criteria to be able to order the records. Perhaps by Category?

select tid,name,max(category) from yourtable group by tid,name order by category desc

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-08 : 08:47:55

>>he wants the first record of the same tid and not the max category

Yes. In that case min will give the answer

Madhivanan

Failing to plan is Planning to fail

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-08 : 08:50:21

simbeeosis "I need to extract all transactions with only one tid and also the first row for transactions with multiple splits."

this line means that he wants the first row of the distinct tid's and not the max category

Kapil Arya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-08 : 08:52:46

Well
Thats why I asked the questioner to post some more data to have his exact requirement

Madhivanan

Failing to plan is Planning to fail

simbeeosis
Starting Member

10 Posts

Posted - 2005-08-08 : 09:21:25

kapil,

the query you provided gave an error. it doesn't like the and in the group by clause.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-08 : 09:22:46

Did you try the query I posted?

Madhivanan

Failing to plan is Planning to fail

simbeeosis
Starting Member

10 Posts

Posted - 2005-08-08 : 09:27:24

here is some real data.
22188,emer,76kdc@i214c24.net,30,21/11/2004,12695
28016,gen,76kdc@i214c24.net,30,09/04/2004,13225
27740,gen,76kdc@i214c24.net,50,21/03/2004,13207
23393,emer,76kdc@i214c24.net,100,19/12/2003,12738
23393,gen,76kdc@i214c24.net,10,19/12/2003,12738

this is what i should get.

22188,emer,76kdc@i214c24.net,30,21/11/2004,12695
28016,gen,76kdc@i214c24.net,30,09/04/2004,13225
27740,gen,76kdc@i214c24.net,50,21/03/2004,13207
23393,emer,76kdc@i214c24.net,100,19/12/2003,12738

please note, the final column is the table's premiary key and so is unique for all rows.

regards

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-08-08 : 09:27:53

quote:

simbeeosis "I need to extract all transactions with only one tid and also the first row for transactions with multiple splits."

this line means that he wants the first row of the distinct tid's and not the max category

[Matrix] There IS NO FIRST ROW [/Matrix]

SQL SERVER does not give a hoot about row numbers. You must supply an ORDER BY clause to get a meaningful "First Row"

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

simbeeosis
Starting Member

10 Posts

Posted - 2005-08-08 : 09:30:29

madhivan,

yes i tried it. it returns too many records when i include other columns in the order by clause

simbeeosis
Starting Member

10 Posts

Posted - 2005-08-08 : 09:33:18

donatwork,

this is why i used cursor. i could think of no other way of being able to deal with the dataset row by row.
i thought of derived tables but could think of no way of coding what i needed.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-08 : 09:43:15

Post your sample data with column names

Madhivanan

Failing to plan is Planning to fail

simbeeosis
Starting Member

10 Posts

Posted - 2005-08-08 : 09:45:01

tid,category,email,gfdate,pkey
22188,emer,76kdc@i214c24.net,30,21/11/2004,12695
28016,gen,76kdc@i214c24.net,30,09/04/2004,13225
27740,gen,76kdc@i214c24.net,50,21/03/2004,13207
23393,emer,76kdc@i214c24.net,100,19/12/2003,12738
23393,gen,76kdc@i214c24.net,10,19/12/2003,12738

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-08 : 09:51:18

Try this

Select Distinct tid,category,(Select top 1 email from yourtable where tid=T.tid),
gfdate,pkey from yourtable T

Madhivanan

Failing to plan is Planning to fail

simbeeosis
Starting Member

10 Posts

Posted - 2005-08-08 : 09:54:57

Select Distinct tid,category,(Select top 1 email from yourtable where tid=T.tid),
gfdate,pkey from yourtable T

this returns all rows from the table

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources