Unique identifier is also a field I need, is this causing my query to return duplicate results?

Kinetic ERP
1

Using Epicor System Version 11.2.400.11 UX Platform Version 10.1.64

I’ve created a custom BAQ. I’m trying to run an Inventory report using 3 tables. Table 1 = PartLot, Table 2 = Part, Table 3 = PartBin.

I need the following fields returned: Class ID, IUM, PN, ProdCode, TypeCode, OnhandQty, LotNum, ExpirationDate, WarehouseCode.

image
image1010×432 20.5 KB

My query results return duplicate records for PN (part number) field every time no matter how I change the JOINS. Is it because Part Number also = unique identifier??

image

The relationship between the tables is Company and PartNum.

image
image697×497 18.7 KB

It becomes a bit confusing to me when joining more than two tables.

I tried using select DISTINCT but that did not work., I even tried DISTINCT at the Table Criteria level. did not work either.

Here’s my Query Phrase & Results. Any help in how to run this correctly is GREATLY appreciated. Thank You!

select
[Part].[ClassID] as [Part_ClassID],
[Part].[IUM] as [Part_IUM],
[Part].[PartNum] as [Part_PartNum],
[Part].[ProdCode] as [Part_ProdCode],
[Part].[TypeCode] as [Part_TypeCode],
[PartLot].[LotNum] as [PartLot_LotNum],
[PartLot].[ExpirationDate] as [PartLot_ExpirationDate],
[PartWhse].[WarehouseCode] as [PartWhse_WarehouseCode]

from Erp.PartLot as [PartLot]
left outer join Erp.Part as [Part] on
PartLot.Company = Part.Company
and PartLot.PartNum = Part.PartNum
inner join Erp.PartWhse as [PartWhse] on
Part.Company = PartWhse.Company
and Part.PartNum = PartWhse.PartNum
order by Part.PartNum

image
image1816×941 59.8 KB

2

Try doing it this way

select 
	[Part].[ClassID] as [Part_ClassID],
	[Part].[IUM] as [Part_IUM],
	[Part].[PartNum] as [Part_PartNum],
	[Part].[ProdCode] as [Part_ProdCode],
	[Part].[TypeCode] as [Part_TypeCode],
	[PartBin].[OnhandQty] as [PartBin_OnhandQty],
	[PartBin].[LotNum] as [PartBin_LotNum],
	[PartLot].[ExpirationDate] as [PartLot_ExpirationDate],
	[PartBin].[WarehouseCode] as [PartBin_WarehouseCode]
from Erp.PartBin as PartBin
inner join Erp.Part as Part on 
	PartBin.Company = Part.Company
	and PartBin.PartNum = Part.PartNum
inner join Erp.PartLot as PartLot on 
	PartBin.Company = PartLot.Company
	and PartBin.PartNum = PartLot.PartNum
	and PartBin.LotNum = PartLot.LotNum

image

3

well this is very exciting! It looks like I’m almost there OR am I there? Does the WHERE make a difference?

I still can see duplicate Part Numbers HOWEVER, they have unique lot numbers so I’ve definitely made progress. I just need to verify we actually have the number of returned records in-house.

Thank you so much!

Here is my Query Phrase:

select
[Part].[ClassID] as [Part_ClassID],
[Part].[IUM] as [Part_IUM],
[Part].[PartNum] as [Part_PartNum],
[Part].[ProdCode] as [Part_ProdCode],
[Part].[TypeCode] as [Part_TypeCode],
[PartBin].[OnhandQty] as [PartBin_OnhandQty],
[PartBin].[LotNum] as [PartBin_LotNum],
[PartLot].[ExpirationDate] as [PartLot_ExpirationDate],
[PartBin].[WarehouseCode] as [PartBin_WarehouseCode]

from Erp.PartBin as [PartBin]
inner join Erp.Part as [Part] on
PartBin.Company = Part.Company
and PartBin.PartNum = Part.PartNum
inner join Erp.PartLot as [PartLot]
where ( PartBin.Company = PartLot.Company
and PartBin.PartNum = PartLot.PartNum
and PartBin.LotNum = PartLot.LotNum )

4

Is your where clause the join between PartBin and PartLot?

When I created it the Query Phrase was what I sent which didn’t have the where.

The query should show duplicate part numbers but since they have unique lot numbers they are unique rows which is good.

I would think if you only had the partbin table and had display fields of PartNum, BinNum, LotNum, and WhseCode you should get the same amount of results as you do with the other two tables joined.

I had a different number since we had some blank lot numbers and this will only return results where the lotnum exists. If you change the join from PartBin to PartLot to a left join it will show you results where the lotnum field is blank which may be helpful depending on what you are looking for.