loop returns duplicates rows when batch is repeated in invent trans table

(0) Share

Report

Posted on by CU17120448-0

This is my while

while select packingSlipJour
where packingSlipJour.RecId == packingSlipJourId.RecId
join packingSlipTrans
where packingSlipJour.PackingSlipId == packingSlipTrans.PackingSlipId
join transOrigin
where packingSlipTrans.InventTransId == transOrigin.InventTransId
join inventTrans
where inventTrans.InventTransOrigin == transOrigin.RecId
join inventDim
where inventTrans.InventDimId == inventDim.InventDimId
join inventBatch
where inventDim.InventBatchId == inventBatch.InventBatchId
&& inventDim.DataAreaId == inventBatch.DataAreaId
join inventTable
where inventTable.ItemId == transOrigin.ItemId
join productTranslation
where productTranslation.Product == inventTable.Product
join inventLocation
where inventLocation.InventLocationId == inventDim.InventLocationId and this my output

I want distinct rows.

Categories:

Supply Chain Management

All responses (1)

Answers (0)

Suggested answer

Martin Dráb 230,253 Most Valuable Professional on at

Like (0)

Report

loop returns duplicates rows when batch is repeated in invent trans table

You need to analyze which table returns more rows when you expect just one. You can, for example, look at RecId of each table in the result set. You can also remove data sources one by one to see which one is to blame.

I immediately see one problem. You're joining with EcoResProductTranslation table, where you may have multiple values for the product. If you want your query to return just one, add a filter for the language.

By the way, throw away the condition inventDim.DataAreaId == inventBatch.DataAreaId. It's applied automatically (that's why you didn't have to add it for all the other table either).