# Help needed with SQL (epicor tables / fields)

**Category:** [Epicor ERP 9](https://www.epiusers.help/c/e9/7)
**Created:** 2017-01-17 09:48 UTC
**Views:** 1018
**Replies:** 3
**URL:** https://www.epiusers.help/t/help-needed-with-sql-epicor-tables-fields/36917

---

## Post #1 by @Rowley150

**Epicor version** 9.05.401

**Problem:** I have an excel spreadsheet that provides me various information from the tables/fields in the SQL script below. The problem is, i'm getting duplicate rows.

**What I need:**  The duplicate rows are because it is returning a row for every warehouse that is linked to the part, so **partwhse.demandqty**...........what i would like the SQL to do is only return 1 row for all warehouses and just sum the demandqty field.
.
.
.

SELECT POHeader.Company, POHeader.ApprovalStatus, POHeader.PONum, PODetail.POLine, Vendor.Name, PODetail.PartNum, PODetail.OrderQty, PartWhse.DemandQty, PartPlant.MinimumQty, PartPlant.SafetyQty, PartPlant.LeadTime, PartPlant.MinOrderQty

FROM epicor905.dbo.Part Part, epicor905.dbo.PartPlant PartPlant, epicor905.dbo.PartWhse PartWhse, epicor905.dbo.PODetail PODetail, epicor905.dbo.POHeader POHeader, epicor905.dbo.Vendor Vendor

WHERE PODetail.Company = POHeader.Company AND PODetail.PONUM = POHeader.PONum AND Vendor.Company = PODetail.Company AND Vendor.Company = POHeader.Company AND Vendor.VendorNum = PODetail.VendorNum AND Vendor.VendorNum = POHeader.VendorNum AND Part.Company = PODetail.Company AND Part.Company = POHeader.Company AND Part.Company = Vendor.Company AND Part.PartNum = PODetail.PartNum AND PartPlant.Company = Part.Company AND PartPlant.Company = PODetail.Company AND PartPlant.Company = POHeader.Company AND PartPlant.Company = Vendor.Company AND PartPlant.PartNum = Part.PartNum AND PartPlant.PartNum = PODetail.PartNum AND PartPlant.VendorNum = Vendor.VendorNum AND PartWhse.Company = Part.Company AND PartWhse.Company = PartPlant.Company AND PartWhse.Company = PODetail.Company AND PartWhse.Company = POHeader.Company AND PartWhse.Company = Vendor.Company AND PartWhse.PartNum = Part.PartNum AND PartWhse.PartNum = PartPlant.PartNum AND PartWhse.PartNum = PODetail.PartNum AND ((POHeader.Company='Prior') AND (POHeader.ApprovalStatus='P'))


Many thanks in advance.

---

## Post #2 by @josecgomez

You need to add a group by clause and use the Sum aggregate function to get your total

---

## Post #3 by @joerojas

Hello,

You want to replace the epicor905.dbo.PartWhse in the FROM clause with a subquery. I normally do all my joining in the FROM clause as opposed to the WHERE clause but I’m thinking the following will work. I don’t have access to E10 to verify though.

SELECT POHeader.Company, POHeader.ApprovalStatus, POHeader.PONum, PODetail.POLine, Vendor.Name, PODetail.PartNum, PODetail.OrderQty, PartWhse.DemandQty, PartPlant.MinimumQty, PartPlant.SafetyQty, PartPlant.LeadTime, PartPlant.MinOrderQty

FROM epicor905.dbo.Part Part, epicor905.dbo.PartPlant PartPlant, (SELECT Company, PartNum, SUM(DemandQty) AS DemandQty FROM epicor905.dbo.PartWhse GROUP BY Company, PartNum) PartWhse, epicor905.dbo.PODetail PODetail, epicor905.dbo.POHeader POHeader, epicor905.dbo.Vendor Vendor

WHERE PODetail.Company = POHeader.Company AND PODetail.PONUM = POHeader.PONum AND Vendor.Company = PODetail.Company AND Vendor.Company = POHeader.Company AND Vendor.VendorNum = PODetail.VendorNum AND Vendor.VendorNum = POHeader.VendorNum AND Part.Company = PODetail.Company AND Part.Company = POHeader.Company AND Part.Company = Vendor.Company AND Part.PartNum = PODetail.PartNum AND PartPlant.Company = Part.Company AND PartPlant.Company = PODetail.Company AND PartPlant.Company = POHeader.Company AND PartPlant.Company = Vendor.Company AND PartPlant.PartNum = Part.PartNum AND PartPlant.PartNum = PODetail.PartNum AND PartPlant.VendorNum = Vendor.VendorNum AND PartWhse.Company = Part.Company AND PartWhse.Company = PartPlant.Company AND PartWhse.Company = PODetail.Company AND PartWhse.Company = POHeader.Company AND PartWhse.Company = Vendor.Company AND PartWhse.PartNum = Part.PartNum AND PartWhse.PartNum = PartPlant.PartNum AND PartWhse.PartNum = PODetail.PartNum AND ((POHeader.Company='Prior') AND (POHeader.ApprovalStatus='P'))

JOE ROJAS
Epicor Applications Manager
VENTION MEDICAL + DESIGN & DEVELOPMENT
DIRECT: 508.597.1392 x1625  |  MOBILE: 774.826.9245

---

## Post #4 by @Matthew_Lawless

For what its worth, Epicor recommends to use with (nolock) on SQL queries.

---


**Canonical:** https://www.epiusers.help/t/help-needed-with-sql-epicor-tables-fields/36917
**Original content:** https://www.epiusers.help/t/help-needed-with-sql-epicor-tables-fields/36917