Sql Cursors is there a simpler approach

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)

Sql Cursors is there a simpler approach

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-21 : 08:47:21

Bharti writes "Ok if you do this query
SELECT TrackItems.FirmFile,
TrackItems.What,
TrackItems.CpDt,
TrackItems.CpBy
FROM TrackItems INNER JOIN
Search ON TrackItems.FirmFile =Search.FirmFile
WHERE (TrackItems.FirmFile = '052-204009' and cpdt <> 0 )

data

052-204009 Blank 20040302 dwatts
052-204009 Pre Commitment 20040729 dclark
052-204009 Blank 20040226 sfreebern
052-204009 Update Search 20040727 mkirk
052-204009 Update Commitment Prepared 20040730 brabeneck

Now I need to display data like the follows

052-204009 Pre Commitment Update Search 20040729 dclark 20040727

I could do this using a cursor and the following method..

declare @PE varchar (30)

declare c1 cursor for (Select trackitems.what from trackitems where firmfile ='052-204040'
and trackitems.what like 'Pre Commitment')
open c1
fetch next from c1 into @PE
while @@fetch_status =0
begin
SELECT TrackItems.FirmFile,
TrackItems.What,
TrackItems.CpDt,
TrackItems.CpBy, @PE as PRE_Commitment_Event
FROM TrackItems INNER JOIN
Search ON TrackItems.FirmFile =Search.FirmFile
WHERE (TrackItems.FirmFile = '052-204040' and cpdt <> 0 and
( Trackitems.what like 'Title Search' or
Trackitems.what like 'Foreclosure Title Search' or
Trackitems.what like 'Update Search'
)
)

fetch next from c1 into @Pe
end
close c1
deallocate c1"

sabirpatel
Starting Member

22 Posts

Posted - 2004-09-22 : 04:29:00

I think what you did is OK. I cannot figure out any other way to do it. You can try out creating a view and use it..i don't know how but you can try that out.

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-22 : 07:51:40

Im not sure i fully understand what your code. But couldnt you use a correlated sub-query in your select list? Not sure if it would be faster then a cursor or not.

here is an example.

Customers
CustomerID
Name

Orders
OrderID
CustomerID
ProductID


-- selecting order count along with customer information
-- this could be done with a group by but it just demonstrates it.
select 
(select count(*) from orders where Orders.CustomerID=Customers.CustomerID) as OrderCount,
CustomerID,
Name
FROM Customers

you can apply this technique so long as the select statement returns one result per record in your main result set.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources