Help with cursor
-
Hello Friends, I have never written a cursor before and I have a situation in which I need it. I have two days trying and still couldn't make it work...I need some help I have three tables one for Items, one for Orders and one for OrderDetail. I want to check every X days that all the unpaid orders(which has items blocked on it, defined in OrderDetail) free their items and increase the amount on the Items table. Evey time a customer make an order, the items are reserved, so they need to be freed if the order is canceled or remains unpaid for a given time. In this case I need a cursor that iterates through all the items belonging to the same unpaid order, then increase the amount in the Items table and delete the record in the OrderDetail table. Please, could you help me in this problem?? I will appreciate any help Thanks.
-
Hello Friends, I have never written a cursor before and I have a situation in which I need it. I have two days trying and still couldn't make it work...I need some help I have three tables one for Items, one for Orders and one for OrderDetail. I want to check every X days that all the unpaid orders(which has items blocked on it, defined in OrderDetail) free their items and increase the amount on the Items table. Evey time a customer make an order, the items are reserved, so they need to be freed if the order is canceled or remains unpaid for a given time. In this case I need a cursor that iterates through all the items belonging to the same unpaid order, then increase the amount in the Items table and delete the record in the OrderDetail table. Please, could you help me in this problem?? I will appreciate any help Thanks.
Most of the time, when I see somebody say they need a cursor I can't help thinking that they've missed out on the alternatives. This seems to be the case here. You probably don't need to use a cursor. What you could do is to create a temporary (or in memory) table that contains details of all of the unfilled orders. Then, once you have done this, you would write your update statements for the other tables based on this table. What you will find is that this approach is more efficient because it allows the database engine to operate in a set based manner which is what DB engines are designed to do. As an example (in pseudocode)
SELECT the missing records FROM orderDetails INTO a Temporary Table. UPDATE order WITH THE itemTotals FROM Temporary Table WHERE THE Item Id IN Temporary Table MATCHES THE Id in my Item TABLE. Do the other tables here...Anyway, that's what I would do.
Deja View - the feeling that you've seen this post before.
-
Most of the time, when I see somebody say they need a cursor I can't help thinking that they've missed out on the alternatives. This seems to be the case here. You probably don't need to use a cursor. What you could do is to create a temporary (or in memory) table that contains details of all of the unfilled orders. Then, once you have done this, you would write your update statements for the other tables based on this table. What you will find is that this approach is more efficient because it allows the database engine to operate in a set based manner which is what DB engines are designed to do. As an example (in pseudocode)
SELECT the missing records FROM orderDetails INTO a Temporary Table. UPDATE order WITH THE itemTotals FROM Temporary Table WHERE THE Item Id IN Temporary Table MATCHES THE Id in my Item TABLE. Do the other tables here...Anyway, that's what I would do.
Deja View - the feeling that you've seen this post before.
Thanks Pete, I will be more specific, I tried your suggestion but still doesn't work...I'm not an SQL expert :o) I will rite here the involved tables and their main fields: Orders(OrderId) OrderDetail(OrderId, ProductSubCode, Quantity) Products(ProductSubCode, Quantity) When an order is canceled I want to get all the items on it and re-insert them in the Product table (summarizing with the amount already in that table) If I do a select of all the items in a given order as you told me I will get something like this: "SELECT * INTO #TMP FROM ORDERDETAIL WHERE ORDERID=30" OrderId ProductSubCode Quantity 30 BD0002 10 30 BD0004 4 Now I need to re-insert those quantities into the Products table. I can't figure out how I ca do that without a cursor since I need to iterate through the records in the temporary table. Of course I can still be missing alternatives Thanks a lot.
-
Thanks Pete, I will be more specific, I tried your suggestion but still doesn't work...I'm not an SQL expert :o) I will rite here the involved tables and their main fields: Orders(OrderId) OrderDetail(OrderId, ProductSubCode, Quantity) Products(ProductSubCode, Quantity) When an order is canceled I want to get all the items on it and re-insert them in the Product table (summarizing with the amount already in that table) If I do a select of all the items in a given order as you told me I will get something like this: "SELECT * INTO #TMP FROM ORDERDETAIL WHERE ORDERID=30" OrderId ProductSubCode Quantity 30 BD0002 10 30 BD0004 4 Now I need to re-insert those quantities into the Products table. I can't figure out how I ca do that without a cursor since I need to iterate through the records in the temporary table. Of course I can still be missing alternatives Thanks a lot.
OK - here's a sample way to update the product table:
UPDATE Products SET p.Quantity = p.Quantity + t.Quantity FROM Products p, #Tmp t WHERE p.ProductSubCode = t.ProductSubCodeThis doesn't involve any cursors and is completely set based.
Deja View - the feeling that you've seen this post before.
-
OK - here's a sample way to update the product table:
UPDATE Products SET p.Quantity = p.Quantity + t.Quantity FROM Products p, #Tmp t WHERE p.ProductSubCode = t.ProductSubCodeThis doesn't involve any cursors and is completely set based.
Deja View - the feeling that you've seen this post before.
-
No problems. I'm glad that I could help.
Deja View - the feeling that you've seen this post before.