Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
COURSE of the MONTH
15 days, 8 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
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 C.ComponentID,
(SELECT SUM(IsNull(OrderQuantity, 0)) - SUM(IsNull(ReceiveQuantity
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.
I have the following query below am using the query below:
Select C.ComponentID,
(SELECT SUM(IsNull(OrderQuantity, 0)) - SUM(IsNull(ReceiveQuantity
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
- +2
6 Comments
You are using table alias names rather than column names. Try:
Select C.ComponentID,
(SELECT SUM(IsNull(OrderQuantity, 0)) - SUM(IsNull(ReceiveQuantity
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
Select C.ComponentID,
(SELECT SUM(IsNull(OrderQuantity, 0)) - SUM(IsNull(ReceiveQuantity
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
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.
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.
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
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
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
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
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
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
Featured Post
Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Why is this different from all of the other step by step guides? Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month15 days, 8 hours left to enroll
741 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.