Microsoft SQL Sub Query - How do I use a "where" clause on a "Sum" column
Posted on 2007-11-29
I am using Microsoft SQL Server 2000.
I have the following query below am using the query below:
(SELECT SUM(IsNull(OrderQuantity, 0)) - SUM(IsNull(ReceiveQuantity, 0))
FROM PurchaseOrderItemTable I
WHERE C.ComponentID = I.ComponentID AND I.ItemStatusID IN (0, 2)) AS ComponentsOnOrder,
(SELECT SUM(IsNull(Quantity * PT.ProductionQuantity, 0))
FROM ProductionItemTable P LEFT OUTER JOIN
ProductionTable PT ON PT.ProductionID = P.LinkID
WHERE C.ComponentID = P.ItemLinkID AND P.ItemTypeID = 1 AND PT.StatusID = 1) AS ComponentsAllocated
From ComponentTable C
where C.StockHolding = 1
(C.StockOnHand - ComponentsAllocated) + ComponentsOnOrder) < C.ReorderLevel
The problem is that I am unable to use the "ComponentsAllocated" or "ComponentsOnOrder" in my final "Where" clause as I get the error message :
"Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ComponentsAllocated'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'ComponentsOnOrder'."
I know the query works as if I remove the last two line all is OK.
The query is used to retun a list of "ComponentID"s from the ComponentTable and the two sub-queries are calculating the Components OnOrder and the Components Allocated from the respective tables for Each Component.
The last two lines of the query only return components that need to be re-ordered.
Any help would be much appreciated.