Help Eliminate Cursor please? - 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

Transact-SQL (2000)

Help Eliminate Cursor please?

Author

Topic

JamesT
Yak Posting Veteran

97 Posts

Posted - 2003-08-25 : 12:39:10

I have the following code that is a conversion from one old system into another. The situation is this: An order header table with iden as PK. An order detail table with FK to headers PK. Each row of the detail that is associated with the FK should have a line number which essentially is a listing of the items in the order. It's running about 70+ hours so far and still is about half-way done. Suggestions are very welcome:

declare @order_iden nvarchar(12)
declare order_iden_cur cursor for
select distinct ord_no
from order_header

open order_iden_cur
fetch next from order_iden_cur into @order_iden
while @@fetch_status = 0
begin
select ord_no = @Order_iden, IDENTITY(int,1,1) as Line_Nbr, IDEN
into #OrderDetail
from order_detail
where ord_no = @Order_Iden

update order_detail
set Line_Nbr = t.Line_Nbr
from order_detail o
inner join #OrderDetail t on o.ord_no = t.ord_no
and o.IDEN = t.IDEN

drop table #OrderDetail
fetch next from order_iden_cur into @order_iden
end

close order_iden_cur
deallocate order_iden_cur

Suggestions are very much appreciated.

Thanks.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-08-25 : 13:03:35

Well this should get someone started. I started working on this, but my lunch time ran out.

Why do you need the Line Number? What is it used for? What determines what order the rows are in the list?

Maybe you can just sort them by a creationdate or something like that and get them "in order" without having to put this field on there.


CREATE TABLE #Order(OrderNum INT, OrderDesc VARCHAR(50))
CREATE TABLE #OrderDetail(OrderNum INT, ItemDescription VARCHAR(50))

INSERT INTO #Order(OrderNum, OrderDesc) VALUES(1, 'Order 1')
INSERT INTO #Order(OrderNum, OrderDesc) VALUES(2, 'Order 2')
INSERT INTO #Order(OrderNum, OrderDesc) VALUES(3, 'Order 3')


INSERT INTO #OrderDetail(OrderNum, ItemDescription) VALUES(1, '5lbs of nuts')
INSERT INTO #OrderDetail(OrderNum, ItemDescription) VALUES(1, '5lbs of bolts')
INSERT INTO #OrderDetail(OrderNum, ItemDescription) VALUES(1, '20 Widgets')



INSERT INTO #OrderDetail(OrderNum, ItemDescription) VALUES(2, '40 Widgets')
INSERT INTO #OrderDetail(OrderNum, ItemDescription) VALUES(2, '5lbs peaunt butter')
INSERT INTO #OrderDetail(OrderNum, ItemDescription) VALUES(2, '55gal Oil')
INSERT INTO #OrderDetail(OrderNum, ItemDescription) VALUES(2, '1 Live Yak')

INSERT INTO #OrderDetail(OrderNum, ItemDescription) VALUES(3, '1 Pear Tree')
INSERT INTO #OrderDetail(OrderNum, ItemDescription) VALUES(3, '1 Partridge')


SELECT o.OrderNum, o.OrderDesc, od.ItemDescription
FROM #Order o
INNER JOIN #OrderDetail od ON od.OrderNum = o.OrderNum


DROP TABLE #OrderDetail
DROP TABLE #Order

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-25 : 13:20:44

I agree with Michael's suggestion. Do you really need the LineNumber column that causes so much grief? Like Michael suggests, you could use a Datetime column during the insert that could serve well to order the rows. And then if you really need the LineNumber column you could use a query similar to the one used to generate ranks:

SELECT OrderID, ProductID,
(SELECT COUNT(*) FROM OrderDetails b WHERE b.OrderID = a.OrderID
AND b.DatetimeCreated >= a.DatetimeCreated) + 1 AS LineNumber
FROM OrderDetails a

You could also write an UPDATE along the same lines. This might a little slow, but I think it'll finish long before 140 hours

Owais

JamesT
Yak Posting Veteran

97 Posts

Posted - 2003-08-26 : 10:51:37

I don't really NEED the line number but you know how it goes when you inherit a system that confines your creativity. The line number is a way to identify each item that is on an order. For example a customer purchases three items. They will have one record in the order header table and three records in the order detail table with each one in the detail with a unique line number count such as 1, 2, and 3. This is required by the existing system that we are converting the data for. Thanks for the reply however and I'm still working on this issue.

James

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources