BAQ with CTE and Union subquery returning too many rows

Kinetic ERP
1

I’ve inherited a BAQ for a consultant that works but is returning too much duplicate information and I can’t figure it out. Its a query for a report to pull out all parts that are flagged as a spare part in all multi-level BOMs on a sales order. It consists of a top level query, a CTE and a Union All subquery.
The query works but what I’ve found is that it is returning a row for each part revision of the upper level part number instead of just the revision that is for that part number on the order detail of the sales order.
Here is the query phrase. We are using the field Part.IsGiftCard to designate a part as a spare part.

(select 
	[PartMtl].[Company] as [PartMtl_Company],
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
	[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
	[Part].[PartDescription] as [Part_PartDescription],
	[Part].[IsGiftCard] as [Part_IsGiftCard],
	(PartCost.AvgLaborCost+ PartCost.AvgBurdenCost+ PartCost.AvgMaterialCost+ PartCost.AvgSubContCost+ PartCost.AvgMtlBurCost) as [Calculated_AVGCost],
	(Convert(Decimal(15,2), OrderDtl.OrderQty * PartMtl.QtyPer)) as [Calculated_QtyReq],
	[OrderDtl].[RevisionNum] as [OrderDtl_RevisionNum],
	[Customer].[Name] as [Customer_Name],
	[Part].[TypeCode] as [Part_TypeCode],
	[Part].[ClassID] as [Part_ClassID],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
	[PartMtl].[RevisionNum] as [PartMtl_RevisionNum]
from Erp.PartMtl as PartMtl
inner join Erp.OrderDtl as OrderDtl on 
	PartMtl.Company = OrderDtl.Company
	and PartMtl.PartNum = OrderDtl.PartNum
	and PartMtl.RevisionNum = OrderDtl.RevisionNum
	and ( OrderDtl.Company = 'IKI'  )

inner join Erp.Customer as Customer on 
	OrderDtl.Company = Customer.Company
	and OrderDtl.CustNum = Customer.CustNum
inner join Erp.Part as Part on 
	PartMtl.Company = Part.Company
	and PartMtl.MtlPartNum = Part.PartNum
inner join Erp.PartCost as PartCost on 
	PartMtl.Company = PartCost.Company
	and PartMtl.MtlPartNum = PartCost.PartNum
union all
select 
	[PartLvl].[PartMtl_Company] as [PartMtl_Company],
	[PartLvl].[OrderDtl_OrderNum] as [OrderDtl_OrderNum],
	[PartLvl].[OrderDtl_OrderLine] as [OrderDtl_OrderLine],
	[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
	[Part1].[PartDescription] as [Part1_PartDescription],
	[PartLvl].[Part_IsGiftCard] as [Part_IsGiftCard],
	(PartCost1.AvgLaborCost+ PartCost1.AvgBurdenCost+ PartCost1.AvgMaterialCost+ PartCost1.AvgSubContCost+ PartCost1.AvgMtlBurCost) as [Calculated_AVGCost],
	(Convert(Decimal(15,2),PartMtl1.QtyPer * PartLvl.Calculated_QtyReq)) as [Calculated_QtyReq],
	[PartRev].[RevisionNum] as [PartRev_RevisionNum],
	[PartLvl].[Customer_Name] as [Customer_Name],
	[PartLvl].[Part_TypeCode] as [Part_TypeCode],
	[PartLvl].[Part_ClassID] as [Part_ClassID],
	[PartLvl].[OrderDtl_PartNum] as [OrderDtl_PartNum],
	[PartLvl].[OrderDtl_LineDesc] as [OrderDtl_LineDesc],
	[PartMtl1].[RevisionNum] as [PartMtl1_RevisionNum]
from  PartLvl  as PartLvl
inner join Erp.PartMtl as PartMtl1 on 
	PartLvl.PartMtl_Company = PartMtl1.Company
	and PartLvl.PartMtl_MtlPartNum = PartMtl1.PartNum
inner join Erp.Part as Part1 on 
	PartMtl1.Company = Part1.Company
	and PartMtl1.MtlPartNum = Part1.PartNum
inner join Erp.PartRev as PartRev on 
	Part1.Company = PartRev.Company
	and Part1.PartNum = PartRev.PartNum
	and ( PartRev.Approved = true  )

inner join Erp.PartCost as PartCost1 on 
	PartMtl1.Company = PartCost1.Company
	and PartMtl1.PartNum = PartCost1.PartNum)

select 
	[PartLvl1].[PartMtl_Company] as [PartMtl_Company],
	[PartLvl1].[OrderDtl_OrderNum] as [OrderDtl_OrderNum],
	[PartLvl1].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
	[PartLvl1].[Part_PartDescription] as [Part_PartDescription],
	(avg(PartLvl1.Calculated_AVGCost)) as [Calculated_AverageCost],
	(sum(PartLvl1.Calculated_QtyReq)) as [Calculated_QuantityRequired],
	[PartLvl1].[Customer_Name] as [Customer_Name],
	[PartLvl1].[Part_TypeCode] as [Part_TypeCode],
	[PartLvl1].[Part_ClassID] as [Part_ClassID],
	[PartLvl1].[OrderDtl_PartNum] as [OrderDtl_PartNum],
	[PartLvl1].[OrderDtl_LineDesc] as [OrderDtl_LineDesc],
	[PartLvl1].[PartMtl_RevisionNum] as [PartMtl_RevisionNum]
from  PartLvl  as PartLvl1
where (PartLvl1.Part_IsGiftCard = 1  and PartLvl1.OrderDtl_OrderNum = 25989)
group by [PartLvl1].[PartMtl_Company],
	[PartLvl1].[OrderDtl_OrderNum],
	[PartLvl1].[PartMtl_MtlPartNum],
	[PartLvl1].[Part_PartDescription],
	[PartLvl1].[Customer_Name],
	[PartLvl1].[Part_TypeCode],
	[PartLvl1].[Part_ClassID],
	[PartLvl1].[OrderDtl_PartNum],
	[PartLvl1].[OrderDtl_LineDesc],
	[PartLvl1].[PartMtl_RevisionNum]

and here is a sample of the data. Notice the material part is shown for each upper level part until it gets to the parts with more than one revision. Then it displays 1 row for each revision.

image
image1252×272 7.59 KB

What we want is for only the revision of the part that is on the sales order line to be displayed.
I don’t know CTE and Union subqueries well so I can’t figure this one out. TIA

2

The problem comes from the second table added on. The first table is filtered by revision number with an inner join.

Which is probably what was wanted for something else and this got reused.

Link the top level query back to the orderdtl with an inner join and make sure to include the revisionnum
in the join.

1 Like
3

I’ve been late getting back to this. Yes that fixed the query. All I needed to do was include the revision number in the join. I’m half way there now. Thanks.

1 Like