Flawed SQL Procedure

I am using the below procedure to set the field "Completed" to "True" in the table "Orders" only when the customer have paid and received or downloaded all his produts.

~~~~~~~~~~~~~~~~~~~~~~~~~

ALTER PROCEDURESetOrderToCompleted

(@.UserNameVARCHAR(50))

AS

UPDATEOrders

SETCompleted = 1

WHEREUserName = @.UserName

ANDCompleted = 0

~~~~~~~~~~~~~~~~~~~~~~~~~

Which is obviously flawed because I predict a situation where thesame customer ( user1 ) could havetwo different orders, like in the below example, when this procedurewill set incorrectly both fields "Completed" to "True" ( in tableOrders ) forOrderID = 1 and OrderID = 2 when actually thenot-downloadable product "gadget105" wasnot received yet by the customer (Received=False in table "OrderDetails" ).

Observations:

1)Downloadable products likesoftware have their field "Received" set toNULL because theydo not need to be shipped and therefore completing this field is irrelevant.

2) Both orders (OrderID = 1 and 2 ) were made by thesame customer withUserName = "user1".

3) The above procedure is only executed after all the downloadable products of the order have being downloaded by the customer.

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 ?

You woul dprobably need to use the OrderId also in the WHERE clause.. so only the specific orders get "completed"|||

Hi ndinakar

But that is the problem, the procedure itself has to be capable to find out which OrderIDs must set the field Completed to True in the table .

|||

I can obtain the information that all downloadable items were downloaded by the client by verifing the field "RemoveRole" in theCustomerDownload 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 PROCEDURESetOrderToCompleted

(@.UserNameVARCHAR(50))

AS

UPDATEOrders

SETCompleted = 1

WHEREOrderID = (SELECT OrderID

FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrdeID

INNER JOIN CustomerDownload ON Orders.OrderID = CustomerDownload.OrderID

WHERE Orders.UserName = @.UserName

ANDCustomerDownload.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.

|||

If your subquery returs multiple records your query could fail. Perhaps you might want to do an IN instead of "=".

UPDATEOrders

SETCompleted = 1

WHEREOrderID IN (SELECT OrderID

FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrdeID

INNER JOIN CustomerDownload ON Orders.OrderID = CustomerDownload.OrderID

WHERE Orders.UserName = @.UserName

ANDCustomerDownload.RemoveRole='yes'

AND OrderDetails.Received = 1

AND Orders.Completed = 0)

flatten out a normalized child table?

I need to extract Customer Order data, and join it to normalized ship-to
table so I can get their address on a single line/row of data. The Column
names are not importat in that final flat file, just what was in
row1,2,3,...6
GARY C Test Row1
LISA C Test Row2
816 RIVERVIEW PLACE Row3
WASHINGTON, MO 63090 Row4
Row5
Row6
THOMAS H Other-Test Row1
2102 N SHAMROCK RD Row2
BEL AIR, MD 21014 Row3
Row4 ,etc.
I have to account for double names names, and possibably titles, Suite #,
etc.
TIAPlease post DDL with your sample data. What is the key of the table you
posted? What relates the address lines together to make a single
address? Apparently nothing links an address together in the sketch you
posted except for the order in which you typed them out. We know that
tables have no fixed order so it isn't possible to combine the rows
reliably to make addresses out of each one.
If you had an additional column such as contact_name or contact_no for
each address line then you could do something like:
SELECT MAX(CASE WHEN row_num = 1 THEN addr END),
MAX(CASE WHEN row_num = 2 THEN addr END),
MAX(CASE WHEN row_num = 3 THEN addr END),
... etc
FROM your_table
GROUP BY contact_name ;
Hope this helps.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129058354.597302.39690@.g44g2000cwa.googlegroups.com...
> Please post DDL with your sample data. What is the key of the table you
> posted? What relates the address lines together to make a single
> address? Apparently nothing links an address together in the sketch you
> posted except for the order in which you typed them out. We know that
> tables have no fixed order so it isn't possible to combine the rows
> reliably to make addresses out of each one.
> If you had an additional column such as contact_name or contact_no for
> each address line then you could do something like:
> SELECT MAX(CASE WHEN row_num = 1 THEN addr END),
> MAX(CASE WHEN row_num = 2 THEN addr END),
> MAX(CASE WHEN row_num = 3 THEN addr END),
> ... etc
> FROM your_table
> GROUP BY contact_name ;
Thanks