Struggling with SQL view duplicating data

Epicor ERP 9
1

Hello, i have a sql view that is duplicating data on most of / or some of the rows and i’ am finding hard to out what the issue is - any help would be any help would be greatly appreciated

SQL view -

SELECT TOP (100) PERCENT dbo.PartTran.PartNum, dbo.Part.PartDescription, dbo.PartTran.TranType, dbo.PartTran.TranQty, dbo.PartTran.TranDate, dbo.Customer.CustID,
dbo.Customer.Name, dbo.OrderRel.OrderNum, dbo.OrderRel.OrderLine, dbo.OrderRel.OrderRelNum
FROM dbo.Part RIGHT OUTER JOIN
dbo.PartTran LEFT OUTER JOIN
dbo.OrderDtl INNER JOIN
dbo.Customer INNER JOIN
dbo.OrderHed ON dbo.Customer.CustNum = dbo.OrderHed.CustNum AND dbo.Customer.Company = dbo.OrderHed.Company ON
dbo.OrderDtl.Company = dbo.OrderHed.Company AND dbo.OrderDtl.OrderNum = dbo.OrderHed.OrderNum INNER JOIN
dbo.OrderRel ON dbo.OrderDtl.Company = dbo.OrderRel.Company AND dbo.OrderDtl.OrderNum = dbo.OrderRel.OrderNum AND
dbo.OrderDtl.OrderLine = dbo.OrderRel.OrderLine ON dbo.PartTran.OrderRelNum = dbo.OrderRel.OrderRelNum AND
dbo.PartTran.OrderLine = dbo.OrderRel.OrderLine AND dbo.PartTran.OrderNum = dbo.OrderRel.OrderNum AND dbo.PartTran.Company = dbo.OrderRel.Company ON
dbo.Part.Company = dbo.PartTran.Company AND dbo.Part.PartNum = dbo.PartTran.PartNum
WHERE (dbo.PartTran.TranType LIKE ‘stk-cus’ OR
dbo.PartTran.TranType LIKE ‘mfg-cus’) AND (NOT (dbo.PartTran.PartNum LIKE ‘DEL’)) AND (dbo.PartTran.TranQty > 0)
ORDER BY dbo.OrderRel.OrderNum, dbo.OrderRel.OrderLine, dbo.OrderRel.OrderRelNum

2

Lee,

What are you trying to accomplish?

3

Hi Mark,

This view is a how items we have sold to and i off load it into crystal repot, because of the duplication in the view the figure are not correct in my report so its saying i sold 440 when i sold 120.

I checked the data in epicor and yes we sold 120 not 440 so the SQL view has issues.

Thanks in advanced.

4

Check your PartTran records. Anytime a Customer Shipment is checked as Shipped or UnShipped, a PartTran record is created. This would cause duplicates since you are only selecting TranQty > 0.

5

You may want to try an order change in how you are selecting the data. I think your “where” clause is tight enough because you are filtering by the trantype. You may want to also try a sub-query if you are building the view directly on the SQL server. I left out all of the company links, but you know to add those it looks like.

parttran
left outer join
orderrel
on parttran.ordernum = orderrel.ordernum and parttran.orderline = orderrel.orderline
and parttran.orderrelnum = orderrel.orderrelnum
left outer join
orderdtl
on orderrel.ordernum = orderdtl.ordernum and orderrel.orderline = orderdtl.orderline
left outer join
orderhed
on orderdtl.ordernum = orderhed.ordernum
left outer join
customer
on orderhed.custnum = customer.custnum
left outer join
part
on parttran.partnum = part.partnum

6

When do you consider an item sold? Once it’s on an Order? Once it’s shipped? Once it’s Invoiced?

For the reasons @bmgarver gave, I would use InvcDtl or ShipDtl records to make your life much simpler.

Mark W.

7

good question mark, i would of said once shipped.