Orders Query - 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 2005 Forums

Transact-SQL (2005)

Orders Query

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-17 : 15:35:48

This is what I want to achieve in SQL. How can I rewrite the query

DECLARE @Quantity NUMERIC(23,10)

SET @Quantity = SELECT quatity FROM Purchase_Order_Items WHERE po_id = @po_id AND item_id = @item_id

IF @Quantity > SELECT SUM(received) FROM Purchase_Order_Items WHERE po_id = @po_id AND item_id = @item_id

UPDATE [Purchase_Order_Items]
SET
[received] = @qty
WHERE po_id = @po_id AND item_id = @item_id
END

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-17 : 15:50:43

maybe something like this


UPDATE [Purchase_Order_Items]
SET [received] = @qty
WHERE po_id = @po_id AND item_id = @item_id
AND quatity > (SELECT SUM(received) FROM Purchase_Order_Items WHERE po_id = @po_id AND item_id = @item_id)

I haev the feeling there might be a better way

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-17 : 15:58:41

Thanks for the reply.

I did it like this

DECLARE @Quantity NUMERIC(23,10)
SELECT @Quantity = quantity FROM Purchase_Order_Items WHERE po_id = @po_id AND item_id = @item_id

DECLARE @Received NUMERIC(23,10)
SELECT @Received = received FROM Purchase_Order_Items WHERE po_id = @po_id AND item_id = @item_id

IF @Quantity <= @Received
UPDATE [Purchase_Orders]
SET
po_status_id = '-2'
WHERE po_id = @po_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources