I have a BAQ - Top Level is summing the On Hand Qty per part number. A subquery is linked to the Top Level and it is summing the WIP qty on jobs. Below is the code. I have tried many different joins and have had many different outcomes, but not the one I want. If there is a WIP qty of 0 (no jobs) I want it to display a 0 same thing with the On Hand Qty. But if there is a WIP qty and no On Hand Qty I would like it to display a 0 for the On Hand Qty. I know the WIP qty is based on the Job, and the On Hand qty is based on the bins and their quantities - So I’m thinking that this is part of my problem.
Here is the code for Subquery1:
select
[Part].[PartNum] as [Part_PartNum],
(sum( PartBin.OnhandQty )) as [Calculated_OnHandQty],
[SubQuery2].[Calculated_TotWIPQty] as [Calculated_TotWIPQty]
from Erp.Part as Part
inner join (select
[Part1].[PartNum] as [Part1_PartNum],
[JobPart].[JobNum] as [JobPart_JobNum],
(sum( JobPart.WIPQty )) as [Calculated_TotWIPQty]
from Erp.Part as Part1
inner join Erp.JobPart as JobPart on
JobPart.PartNum = Part1.PartNum
group by [Part1].[PartNum],
[JobPart].[JobNum]) as SubQuery2 on
SubQuery2.Part1_PartNum = Part.PartNum
inner join Erp.PartBin as PartBin on
PartBin.Company = Part.Company
And
PartBin.PartNum = Part.PartNum
and ( not PartBin.BinNum like ‘MRB’ or PartBin.OnhandQty >= 0 )
group by [Part].[PartNum]
When I try to run this I receive the following error:
Column ‘SubQuery2.Calculated_TotWIPQty’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
First - is there something blaringly obvious that I’m missing?
I’m not expert - any feedback would be much appreciated.
I always do the aggregate (Sum, Avg, etc) calculations in the subqueries and leave the Top query for the main table with non-aggregate calculations.
Example is this one I did on Part - PartWhse to display part data, mostly UD fields for warehousing, and total the “Quantity on Hand” for each part. Our stock/warehouse guys use this data determining bin locations and when pallet-ing production for shipping.
From the pure TSQl side of things the group by needs to contain all selected columns other than the calculated column. Whilst you think that [SubQuery2].[Calculated_TotWIPQty] is an aggregate/calculated column in the subquery the main query treats the result set of the subquery as if you are joining a real table. so it expects to see Group by [Part].[PartNum],[SubQuery2].[Calculated_TotWIPQty], which may or may not be your desired result.
I hope the information helps.
I do agree with Randy. Keep the queries simple to start with and go from there. Try to keep a consistent writing style as it will save you time in the future, not to mention anyone coming after you.
One thing to make note of is that you can get the same result from writing queries differently but some queries are more efficient than others. This is a great example to see what the difference is between aggregating in the main query vs joining and aggregating in sub-queries only…You could write both then simply run it through the query analyzer… I mean SSMS Query and take a look at the execution plan if you want detail, or simply look at the execution time at the bottom of the screen.
There are lots of places on the web on query optimization and using SSMS and query plans, just google it. Sorry for the cop out reference, but there are simply too many to mention individually.
MSSQL is a fantastic RDMBS and I am forever grateful that Epicor have streamlined the technology stack.
