I am having trouble with duplicate serial numbers in a BAQ I am running. In an instance where a serial number cannot repeat on a specific part they are showing up multiple times. Is there a criteria or something I can add to stop these duplicates from populating.
Are you getting a serial number for each partbin? You may want to change your linkage so that Part is in-between SerialNo and PartBin.
I put Part Between PartBin and SerialNo and fiddled with their join types, but got the same results.
Also, if I remove the TFOrdDtl Table, I get well over 1000 records.
Backing up a bit, what results are you trying to produce?
For a given location, show me all the serial numbers with transfer details? Or…
if your using partbin you will get multiple records if the same serial number exists in multiple bins. If you don’t want info to bin level then use PartWhse instead.
Mark,
I am trying to essentially get an inventory of all sites’ serialized and non serialized inventory.
Paul,
Thats the issue, the serial number does not exist in multiple bins, the part that the serial number is assigned to may exist in multiple bins, but not the serial number. We are talking bins from multiple sites.
I got it closer to having no duplicates, but what i dont understand is why are the duplicates so inconsistently wrong? One part has a qty of 4 and 4 serial numbers. Another part has 3 in stock with 3 serial numbers, but shows all 3 records twice. Another part has 1 in stock but shows 2 records for the same serial number.
You can’t use an inner join between SerialNo and TFOrdDtl. Once you use a left outer join (PartBin and SerialNo), all lower level joins also need to be outer joins. The set up you have will produced unexpected result sets.
What purpose is the TFOrdDtl table serving in your query?
SerialRecursive.baq (9.1 KB)
If anyone wants Recursvie Serial BAQ =) Something I tinkered with - Shows Matched Serial Numbers.
2 Likes
Zack,
I am attaching my most recent iteration of the joins I have put together. I don’t particularly know what left outer join and inner join mean, but this is how I have gotten the least number of duplicates. TFOrdDtl just being attached reduces duplicate records by a great amount. If you have a better suggestions for how to accomplish this I would be glad to give it a shot.
Could you post the query phrase please. Seeing the joins will help to figure things out.
select
[Part].[PartNum] as [Part_PartNum],
[SerialNo].[SerialNumber] as [SerialNo_SerialNumber],
[Part].[PartDescription] as [Part_PartDescription],
[SerialNo].[SNStatus] as [SerialNo_SNStatus],
[PartBin].[BinNum] as [PartBin_BinNum],
[PartBin].[OnhandQty] as [PartBin_OnhandQty]
from Erp.Part as Part
right outer join Erp.SerialNo as SerialNo on
SerialNo.Company = Part.Company
and SerialNo.PartNum = Part.PartNum
and ( SerialNo.SNStatus = ‘’ or SerialNo.SNStatus = ‘INVENTORY’ )
inner join Erp.PartBin as PartBin on
PartBin.Company = Part.Company
and PartBin.PartNum = Part.PartNum
inner join Erp.PartBin as PartBin
and
PartBin.Company = SerialNo.Company
and PartBin.PartNum = SerialNo.PartNum
and PartBin.BinNum = SerialNo.BinNum
inner join Erp.PartQty as PartQty on
PartBin.Company = PartQty.Company
and PartBin.PartNum = PartQty.PartNum
and PartBin.OnhandQty = PartQty.OnHandQty
inner join Erp.TFOrdDtl as TFOrdDtl on
SerialNo.Company = TFOrdDtl.Company
and SerialNo.PartNum = TFOrdDtl.PartNum
and SerialNo.BinNum = TFOrdDtl.StagingBinNum
I am not able to duplicate your issue on the dataset I have here.
What might help is to start with your base and then add tables. When the new table adds more records than you expect that is where you will have to investigate.
I would start with SN table as it has the criteria.
select count(*)
from Erp.SerialNo as SerialNo on
Where SerialNo.SNStatus = '' or SerselialNo.SNStatus = 'INVENTORY' )
Then when you get your count you can start to add the other tables. A SQL editor might be more helpful with debugging this, but it can be done in the BAQ editor as well.
An inner join is when where there are matching records from both sides tables. A left or right join returns records from one table and the matching rows from the other. It looks like you fixed the initial issue, but now you’ve created another problem by creating a circular join between the part, partbin, and SerialNo tables. This will not produce a valid dataset.
The joins on the TFOrdDtl table don’t really relate to the serial numbers at all, so it should proabably be removed from your query altogether.
You should be able to produce a full inventory list from just the PartBin table.
I ended up restarting and simplifying the BAQ. I used only SerialNo, PlantWhse, and Part and now don’t have any duplicate records.
Thanks everyone for the help
1 Like
you are welcome.
That is the best way. Start with one table. Run it. Add one. Run it. Once you get dups, you’ll know which table/link is responsible.