Experts Exchange connects you with the people and services you need so you can get back to work.

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.

Any help would be much appreciated.

saphireneil

Asked: 2007-11-29

Advertise Here

Microsoft SQL Server

1 Solution

LVL 54

Vitor MontalvãoMSSQL Senior EngineerCommented: 2007-11-29

That columns have the same name of your alias to the 2 subqueries.

LVL 27

MikeTooleCommented: 2007-11-29

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

LVL 5

usarianCommented: 2007-11-29

You cant reference subqueries in a WHERE clause.

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.

LVL 5

usarianCommented: 2007-11-29

Try this:

Select C.ComponentID, ComponentsAllocated.Allocated, ComponentsOnOrder.OnOrder
 
From ComponentTable C
 
LEFT 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.LinkID
WHERE P.ItemTypeID = 1 AND PT.StatusID = 1)
   AS ComponentsAllocated ON C.ComponentID = ComponentsAllocated.ItemLinkID
 
LEFT 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

Open in new window

LVL 25

imitchieCommented: 2007-11-29

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 ComponentsAllocated
 
From ComponentTable C
  where C.StockHolding = 1
) DATA
WHERE (C.StockOnHand - ComponentsAllocated) + ComponentsOnOrder < C.ReorderLevel

Open in new window

saphireneilAuthor Commented: 2007-11-29

Apart from a couple of minor syntax changes this is spot on! thanks alot...

I have detailed below the final query below for future ee reference.

Thanks again

Neil :-)

-----------------------------------------------------------------

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Microsoft SQL Sub Query - How do I use a "where" clause on a "Sum" column

Join & Write a Comment Already a member? Login.

Featured Post