PartWhse Sum(OnHandQty)

,

Hello,

I am attempting to get the on hand qty for parts in all warehouses. I thought this was simple but it doesn’t want to work for me. I am using the PartWhse table and the OnHandQty field. My calculated field is SUM(PartWhse.OnHandQty)over (PARTITION BY PartWhse.PartNum). I could not simply do SUM(PartWhse.OnHandQty) as this gave different values for the different warehouse instead I want the sum of three different warehouses.

This gives an error of "Windowed functions can only appear in the SELECT or ORDER BY clauses. This statement is in the SELECT clause. I also tried adding ORDER BY PartWhse.PartNum after the PARTITION BY part of the field. I also tried putting PartWhse into a subquery with the same calculated field. It gave the same error.

What is weird is I know this works because it works in a separate BAQ with PartWhse as the only table. Does this have something to do with the other sub queries or grouping somehow?

Here is the full query: (the PartBinSum subquery was what I used to try PartWhse and PartBin in a subquery)

select
[requirements].[PartDtl_Company] as [PartDtl_Company],
[requirements].[PartDtl_PartNum] as [PartDtl_PartNum],
[PartWhse].[PartNum] as [PartWhse_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[requirements].[Calculated_TotalReq] as [Calculated_TotalReq],
[Supply].[Calculated_totalSupply] as [Calculated_totalSupply],
((ISNULL(Supply.Calculated_totalSupply,0.00)+OnHand)-ISNULL(requirements.Calculated_TotalReq,0.00)) as [Calculated_availableQty],
[PartPlant].[MinimumQty] as [PartPlant_MinimumQty],
[PartPlant].[SafetyQty] as [PartPlant_SafetyQty],
[PartPlant].[MaximumQty] as [PartPlant_MaximumQty],
[Part].[ClassID] as [Part_ClassID],
[PartClass].[Description] as [PartClass_Description],
(availableQty-PartPlant.MinimumQty-PartPlant.SafetyQty) as [Calculated_AvailMinSafe],
(0.00) as [Calculated_PropsedOrder],
(case
when availableQty < 0 then ‘Below Zero’
when availableQty < PartPlant.MinimumQty then ‘Below Minimum’
when availableQty > PartPlant.MaximumQty and PartPlant.MaximumQty > 0 then ‘Over Maximum’
when AvailMinSafe < 0 then ‘Below Reorder’
else null
end) as [Calculated_Exception],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[Name] as [Vendor_Name],
[PartPlant].[LeadTime] as [PartPlant_LeadTime],
[Part].[TypeCode] as [Part_TypeCode],
[PartPlant].[MinOrderQty] as [PartPlant_MinOrderQty],
[PartWhse].[KBCode] as [PartWhse_KBCode],
[PartPlant].[MinMfgLotSize] as [PartPlant_MinMfgLotSize],
[PartPlant].[MaxMfgLotSize] as [PartPlant_MaxMfgLotSize],
[PartPlant].[MfgLotMultiple] as [PartPlant_MfgLotMultiple],
[PartPlant].[DaysOfSupply] as [PartPlant_DaysOfSupply],
[PartPlant].[ReOrderLevel] as [PartPlant_ReOrderLevel],
[Part].[IUM] as [Part_IUM],
(SUM(PartWhse.OnHandQty)over (PARTITION BY PartWhse.PartNum)) as [Calculated_OnHand]
from (select
[PartDtl].[Company] as [PartDtl_Company],
[PartDtl].[PartNum] as [PartDtl_PartNum],
(sum(PartDtl.Quantity)) as [Calculated_TotalReq]
from Erp.PartDtl as PartDtl
left outer join Erp.JobHead as JobHead on
PartDtl.JobNum = JobHead.JobNum
left outer join Erp.JobMtl as JobMtl on
PartDtl.PartNum = JobMtl.PartNum
and PartDtl.JobNum = JobMtl.JobNum
and PartDtl.AssemblySeq = JobMtl.AssemblySeq
and PartDtl.JobSeq = JobMtl.MtlSeq
where (PartDtl.RequirementFlag = 1)
and (JobMtl.BuyIt = 0 or PartDtl.OrderNum <> 0 ) and (JobHead.JobClosed = 0 or PartDtl.OrderNum <> 0 )

group by [PartDtl].[Company],
[PartDtl].[PartNum]) as requirements
left outer join (select
[PartDtl1].[Company] as [PartDtl1_Company],
[PartDtl1].[PartNum] as [PartDtl1_PartNum],
(sum(PartDtl1.Quantity)) as [Calculated_totalSupply]
from Erp.PartDtl as PartDtl1
left outer join Erp.JobHead as JobHead1 on
PartDtl1.JobNum = JobHead1.JobNum
left outer join Erp.JobMtl as JobMtl1 on
PartDtl1.PartNum = JobMtl1.PartNum
and PartDtl1.JobNum = JobMtl1.JobNum
and PartDtl1.AssemblySeq = JobMtl1.AssemblySeq
and PartDtl1.JobSeq = JobMtl1.MtlSeq
where (PartDtl1.RequirementFlag = 0)
and (JobMtl1.BuyIt = 0 or PartDtl1.OrderNum <> 0 ) and (JobHead1.JobClosed = 0 or PartDtl1.OrderNum <> 0 )

group by [PartDtl1].[Company],
[PartDtl1].[PartNum]) as Supply on
requirements.PartDtl_Company = Supply.PartDtl1_Company
and requirements.PartDtl_PartNum = Supply.PartDtl1_PartNum
right outer join Erp.PartWhse as PartWhse on
requirements.PartDtl_Company = PartWhse.Company
and requirements.PartDtl_PartNum = PartWhse.PartNum
left outer join Erp.Part as Part on
PartWhse.Company = Part.Company
and PartWhse.PartNum = Part.PartNum
left outer join Erp.PartClass as PartClass on
Part.Company = PartClass.Company
and Part.ClassID = PartClass.ClassID
left outer join Erp.PartPlant as PartPlant on
PartWhse.Company = PartPlant.Company
and PartWhse.PartNum = PartPlant.PartNum
left outer join Erp.Vendor as Vendor on
PartPlant.Company = Vendor.Company
and PartPlant.VendorNum = Vendor.VendorNum
left outer join (select
[PartWhse1].[PartNum] as [PartWhse1_PartNum],
[PartWhse1].[WarehouseCode] as [PartWhse1_WarehouseCode]
from Erp.PartWhse as PartWhse1
group by [PartWhse1].[PartNum],
[PartWhse1].[WarehouseCode]) as PartBinSum on
PartBinSum.PartWhse1_PartNum = PartWhse.PartNum
where PartWhse.WarehouseCode = ‘0001’ or PartWhse.WarehouseCode = ‘0005’ or PartWhse.WarehouseCode = ‘0003’

group by [requirements].[PartDtl_Company],
[requirements].[PartDtl_PartNum],
[PartWhse].[PartNum],
[Part].[PartDescription],
[requirements].[Calculated_TotalReq],
[Supply].[Calculated_totalSupply],
((ISNULL(Supply.Calculated_totalSupply,0.00)+OnHand)-ISNULL(requirements.Calculated_TotalReq,0.00)),
[PartPlant].[MinimumQty],
[PartPlant].[SafetyQty],
[PartPlant].[MaximumQty],
[Part].[ClassID],
[PartClass].[Description],
(availableQty-PartPlant.MinimumQty-PartPlant.SafetyQty),
[Vendor].[VendorID],
[Vendor].[Name],
[PartPlant].[LeadTime],
[Part].[TypeCode],
[PartPlant].[MinOrderQty],
[PartWhse].[KBCode],
[PartPlant].[MinMfgLotSize],
[PartPlant].[MaxMfgLotSize],
[PartPlant].[MfgLotMultiple],
[PartPlant].[DaysOfSupply],
[PartPlant].[ReOrderLevel],
[Part].[IUM]

make a sub-query (SubQuery2) of just the PartWhse table, returning just 3 fields:

  • Company (GROUP BY)
  • Partnum (GROUP BY)
  • TotalQOH = SUM(OnHandQty)

Then you can reference the subquery (SubQuery2) in your top level query (SubQuery1), relating it to the Part table in SubQuery1:

  • Part.Company=SubQuery2.Company
  • Part.PartNum = SubQuery2.PartNum
  • might want to make this table relationship an Left join (All records from Part, matching from SubQuery2)
1 Like