question

Abhay11 avatar image
Abhay11 asked

Qty from order_tran table should not be greater than qty_in_hand of Items table. how can i manage this?

Qty from order_tran table should not be greater than qty_in_hand of Items table. how can i manage this?
tablestable-variableexisting-table
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
If you just need to compare the 2 tables and return a list of products with an order exceeding the total available, this should help: SELECT t.product_id FROM order_tran t INNER JOIN Items i ON i.product_id = t.product_id WHERE t.Qty > i.qty_in_hand ; If this is a transaction procedure that receives a product ID and quantity desired and then places the order, perhaps this will help: -- @ProductId and @QuantityRequested are parameters DECLARE @QuantityAddedToOrder int; DECLARE @OutputQuantityTable TABLE (value int); BEGIN TRANSACTION; UPDATE Items SET qty_in_hand -= CASE WHEN qty_in_hand > @QuantityRequested THEN @QuantityRequested ELSE qty_in_hand END OUTPUT deleted.qty_in_hand - inserted.qty_in_hand INTO @OutputQuantityTable (value) WHERE product_id = @ProductId ; SET @QuantityAddedToOrder = (SELECT value FROM @OutputQuantityTable); INSERT order_tran (product_id, Qty) VALUES(@ProductId, @QuantityAddedToOrder) ; COMMIT TRAN; SELECT order_status = 'Order confirmed' , full_request_met = CONVERT ( bit , CASE WHEN @QuantityAddedToOrder = @QuantityRequested THEN 1 ELSE 0 END ) , order_quantity = @QuantityAddedToOrder ; If neither of those are what you are looking for, please provide more detail so we can better answer your question. Thanks.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.