March 2, 2007 at 2:48 pm
I am using the below procedure to set the field “Completed” to “True” in the table “Orders”:
~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER PROCEDURE SetOrderToCompleted
(@UserName VARCHAR(50))
AS
UPDATE Orders
SET Completed = 1
WHERE UserName = @UserName
AND Completed = 0
~~~~~~~~~~~~~~~~~~~~~~~~~
Which is obviously flawed because I predict a situation where the same customer ( user1 ) could have two different orders, like in the below example, when this procedure will set incorrectly the both fields “Completed” to “True” ( in table Orders ) for OrderID = 1 and OrderID = 2 when actually the not-downloadable product “gadget105” was not received yet by the customer ( Received = False in table “OrderDetails” ).
Observations:
1) Downloadable products like software have their field “Received” set to NULL because they do not need to be shipped and therefore completing this field is irrelevant.
2) Both orders ( OrderID = 1 and 2 ) were made by the same customer with UserName = “user1”.
Table OrderDetails
_______________________________________________________________
OrderID ProductID ProductName Downloadable Quantity Received UnitCost
1 10 software10 True 1 NULL 15.00
1 101 gadget101 False 1 True 20.00
2 12 software12 True 1 NULL 16.00
2 105 gadget105 False 1 False 22.00
2 13 software13 True 1 NULL 22.00
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Table Orders
_______________________________________________________________
OrderID UserName PaymentConfirmed Completed
1 user1 True False
2 user1 True False
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
How to solve the problem ?
March 2, 2007 at 3:29 pm
Oops, I forgot to add two more observations:
3) I am using the above procedure to set the field “Completed” to “True” in the table “Orders” only when the customer have paid and received or downloaded all his products.
4) The above procedure is only executed after all the downloadable products of the order have being downloaded by the customer and another procedure verified/confirmed that.
March 2, 2007 at 4:57 pm
I can obtain the information that all downloadable items were downloaded by the client by verifing the field “RemoveRole” in the CustomerDownload table ( not shown here ) set to ‘yes’.
Based on that, I devised this new procedure but since I am not good with “INNER JOINs”, can somebody tell me if it is correct ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER PROCEDURE SetOrderToCompleted
(@UserName VARCHAR(50))
AS
UPDATE Orders
SET Completed = 1
WHERE OrderID = ( SELECT OrderID
FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrdeID
INNER JOIN CustomerDownload ON Orders.OrderID = CustomerDownload.OrderID
WHERE Orders.UserName = @UserName
AND CustomerDownload.RemoveRole=’yes’
AND OrderDetails.Received = 1
AND Orders.Completed = 0)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In this procedure I aim to set the field Completed to True for all the customer’s orders that satisfy the above conditions.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply