Here’s my Query Phrase:
select
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[PartPlant].[LeadTime] as [PartPlant_LeadTime],
[VendPart].[VenPartNum] as [VendPart_VenPartNum],
[Vendor].[Name] as [Vendor_Name]
from Erp.PartPlant as [PartPlant]
inner join Erp.Part as [Part] on
PartPlant.Company = Part.Company
and PartPlant.PartNum = Part.PartNum
left outer join Erp.VendPart as [VendPart] on
Part.Company = VendPart.Company
and Part.PartNum = VendPart.PartNum
full outer join Erp.Vendor as [Vendor] on
VendPart.Company = Vendor.Company
and VendPart.VendorNum = Vendor.VendorNum
where (PartPlant.SourceType = ‘P’
and PartPlant.Plant = ‘MfgSys’)
This indicates you have multiple entries for the same part in VendPart. This is normal, as supplier pricing can change over time, as well as the possibility of having multiple supplier part numbers point to a singular part in your system.
You would want to determine if there are any reliable criteria you can place on your VendPart table to return the singular record for each part number you want to reference and display the supplier part.
If you row count goes up, then there is either a bad join or you have some duplicate records in the new table. In this case, I think your joins are solid, so it must be multiple records in the VendPart and/or Vendor table.
Do you have multiple Sites? Does the VendPart table have multiple references for the same PartNum?
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
select
[PartPlant].[Company] as [PartPlant_Company],
[PartPlant].[Plant] as [PartPlant_Plant],
[PartPlant].[PartNum] as [PartPlant_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[IUM] as [Part_IUM],
[Part].[PUM] as [Part_PUM],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[Name] as [Vendor_Name],
[VendPart].[VenPartNum] as [VendPart_VenPartNum],
[PartPlant].[LeadTime] as [PartPlant_LeadTime]
from Erp.PartPlant as PartPlant
inner join Erp.VendPart as VendPart on
PartPlant.Company = VendPart.Company
and PartPlant.PartNum = VendPart.PartNum
and PartPlant.VendorNum = VendPart.VendorNum
inner join Erp.Part as Part on
VendPart.Company = Part.Company
and VendPart.PartNum = Part.PartNum
inner join Erp.Vendor as Vendor on
VendPart.Company = Vendor.Company
and VendPart.VendorNum = Vendor.VendorNum
order by PartPlant.Company, PartPlant.Plant, PartPlant.PartNum
Those are two distinct records. I wouldn’t get too hung up on what the filter you are using there is doing. That looks like the filter was based on a “contains” rather than an “equal”. Your joins don’t work in that same fashion.
On the surface, it looks like one of your users accidentally put this part in two different ways. You have defined BOITH 11570 and 00011570 in your Part Master and I would guess only one of those is supposed to exist. Granted, this is a bug asumption, but with both the part number and description being so similar, that’s my wager.
If it “Matched” This would be populated. It’s not. So it’s not matching those two part numbers. You have a left join, which means it will be empty if there is no match.
Not knowing the exact relationship of your data so it could have an impact, I would try to construct the BAQ as follows with the existing filters. My sequence of tables would be Part, Part Plant, Vendor, and Vendor Part Number with an Inner Join between Part and Part Plant then use an Outer Join for the other two Relationships.
‘determine if there are any reliable criteria you can place on your VendPart table to return the singular record for each part number you want to reference and display the supplier part.’
The BAQ I uploaded has no outer joins…it’s a straight one-to-one so probably shouldn’t have any dups. If PartPlant’s Company/PartNum/VendorNum find a hit in VendPart on the same fields, you should be good.
Unless I bolluxed up the BAQ which is entirely possible.
