Microsoft SQL Sub Query - How do I use a "where" clause on a "Sum" column
I am using Microsoft SQL Server 2000.
I have the following query below am using the query below:
Select C.ComponentID,
(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
and
(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.
You are using table alias names rather than column names. Try:
Select C.ComponentID,
(SELECT SUM(IsNull(OrderQuantity, 0)) - SUM(IsNull(ReceiveQuantity, 0)) As ComponentsOnOrder
FROM PurchaseOrderItemTable I
WHERE C.ComponentID = I.ComponentID AND I.ItemStatusID IN (0, 2)) AS Table1,
(SELECT SUM(IsNull(Quantity * PT.ProductionQuantity, 0)) AS ComponentsAllocated
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 Table2
From ComponentTable C
where C.StockHolding = 1
and
(C.StockOnHand - ComponentsAllocated) + ComponentsOnOrder) < C.ReorderLevel
subqueries are processed row by row returned, the where clause designates the criteria to return the row. You will either have to puyt the whoel subqueries in the where clause or find a way to move the subqueries to JOINs.
0
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Select C.ComponentID, ComponentsAllocated.Allocated, ComponentsOnOrder.OnOrderFrom ComponentTable CLEFT OUTER JOIN(SELECT P.ItemLinkID, SUM(IsNull(Quantity * PT.ProductionQuantity, 0)) AS Allocated FROM ProductionItemTable P LEFT OUTER JOIN ProductionTable PT ON PT.ProductionID = P.LinkIDWHERE P.ItemTypeID = 1 AND PT.StatusID = 1) AS ComponentsAllocated ON C.ComponentID = ComponentsAllocated.ItemLinkIDLEFT OUTER JOIN(SELECT I.ComponentID, SUM(IsNull(OrderQuantity, 0)) - SUM(IsNull(ReceiveQuantity, 0)) AS OnOrder FROM PurchaseOrderItemTable I WHERE I.ItemStatusID IN (0, 2)) AS ComponentsOnOrder ON C.ComponentID = ComponentsOnOrder.ComponentID where C.StockHolding = 1 and(C.StockOnHand - ComponentsAllocated.Allocated) + ComponentsOnOrder.OnOrder) < C.ReorderLevel
What you usually do is wrap the whole thing as another subquery like so
SELECT * FROM(Select C.ComponentID,(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 ComponentsAllocatedFrom ComponentTable C where C.StockHolding = 1) DATAWHERE (C.StockOnHand - ComponentsAllocated) + ComponentsOnOrder < C.ReorderLevel
SELECT * FROM
(
Select C.ComponentID, C.StockOnHand, C.ReorderLevel,
(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
)
DATA
WHERE (StockOnHand - ComponentsAllocated) + ComponentsOnOrder < ReorderLevel
0
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.