Below I have attached the Query and the Result Set and my attempt at the CASE function.
In the result set you'll see it returns
the Qty of a product available in stock as QtyInStock
AND the Qty that was ordered as 'QtyOrdered'.
I want to have another column called 'QtyToDeliver' which works like this:
QtyInStock < QtyOrdered
QtyToDeliver = QtyInStock
QtyToDeliver = QtyOrdered
With my CASE function like it is I get error:
Incorrect syntax near '<'.
Invalid column name 'QtyInStock'.
PRODUCT_ID QtyInStock QtyOrdered
1032 1 1
1016 10 1
1018 0 1
IsNull(Count(Stock.STOCK_ID),0) As QtyInStock,
TRANS_CLIENT_STOCK.TC_ID = 94403
AND TRANS_CLIENT_STOCK.PRODUCT_ID = Product.PRODUCT_ID) AS QtyOrdered
--QtyToDeliver = CASE QtyInStock
--WHEN QtyInStock < QtyOrdered THEN QtyOrdered
LEFT JOIN Stock
ON Product.PRODUCT_ID = Stock.PRODUCT_ID
AND Stock.CLIENT_ID = 691
AND POS_ID IS NULL
AND PickedDate IS NULL
PRODUCT.PRODUCT_ID NOT IN
(Select PRODUCT_ID FROM EXT_TYPE)