Duplicate Records/Lines

Epicor ERP 10
1

I know that this question has been asked quite a lot… But how can I actually reduce multiple/duplicate lines in a query?

I have this query that grabs a certain part and displays the customer and miscellaneous charges that are tied to the order…
Here’s an example pic of one order that displays 3 records -

image.png1598×87 14.8 KB

So is it possible to reduce the 3 records into 1? I would like to have all the data in one line, so the TAX, SHIP, and SO would read 0.010 - 3.070 - -3.070 all on one line.

The query phrase is:

select 
	[Customer].[CustID] as [Customer_CustID],
	[Customer].[Name] as [Customer_Name],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
	((case when ShipMisc.MiscCode = 'TAX' then ShipMisc.MiscAmt else '0.00' end)) as [Calculated_Tax],
	((case when ShipMisc.MiscCode = 'SHIP' then ShipMisc.MiscAmt else '0.00' end)) as [Calculated_Ship],
	((case when ShipMisc.MiscCode = 'SO' then ShipMisc.MiscAmt else '0.00' end)) as [Calculated_SO],
	((case when ShipMisc.MiscCode = 'OTHR' then ShipMisc.MiscAmt else '0.00' end)) as [Calculated_OTHR],
	((case when ShipMisc.MiscCode = 'CNTY' then ShipMisc.MiscAmt else '0.00' end)) as [Calculated_CNTY],
	((case when ShipMisc.MiscCode = 'CITY' then ShipMisc.MiscAmt else '0.00' end)) as [Calculated_City],
	[ShipHead].[ShipDate] as [ShipHead_ShipDate],
	[OrderHed].[OrderDate] as [OrderHed_OrderDate],
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
	[ShipDtl].[PackNum] as [ShipDtl_PackNum],
	[ShipDtl].[PackLine] as [ShipDtl_PackLine]
from Erp.ShipHead as ShipHead
inner join Erp.ShipDtl as ShipDtl on 
	ShipHead.Company = ShipDtl.Company
And
	ShipHead.PackNum = ShipDtl.PackNum

full outer join Erp.OrderDtl as OrderDtl on 
	ShipDtl.Company = OrderDtl.Company
And 
	ShipDtl.OrderNum = OrderDtl.OrderNum
And
	ShipDtl.OrderLine = OrderDtl.OrderLine

inner join Erp.OrderHed as OrderHed on 
	OrderDtl.Company = OrderHed.Company
And
	OrderDtl.OrderNum = OrderHed.OrderNum

inner join Erp.ShipMisc as ShipMisc on 
	ShipHead.Company = ShipMisc.Company
And
	ShipHead.PackNum = ShipMisc.PackNum

inner join Erp.Customer as Customer on 
	OrderHed.Company = Customer.Company
And
	OrderHed.BTCustNum = Customer.CustNum

 where OrderDtl.PartNum like '2SET'

I’ve tried reducing the amount of tables and fields and half the time it increases the amount of records, and the other half it doesn’t have all the fields I need.

Any help would be greatly appreciated

Thanks

2

Use GroupBy and for those fields that are Numeric Like Tax Etc… do a SUM(field)

2 Likes
3

like this

select 
	[Customer].[CustID] as [Customer_CustID],
	[Customer].[Name] as [Customer_Name],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
	SUM((case when ShipMisc.MiscCode = 'TAX' then ShipMisc.MiscAmt else '0.00' end)) as [Calculated_Tax],
	SUM((case when ShipMisc.MiscCode = 'SHIP' then ShipMisc.MiscAmt else '0.00' end)) as [Calculated_Ship],
	SUM((case when ShipMisc.MiscCode = 'SO' then ShipMisc.MiscAmt else '0.00' end)) as [Calculated_SO],
	SUM((case when ShipMisc.MiscCode = 'OTHR' then ShipMisc.MiscAmt else '0.00' end)) as [Calculated_OTHR],
	SUM((case when ShipMisc.MiscCode = 'CNTY' then ShipMisc.MiscAmt else '0.00' end)) as [Calculated_CNTY],
	SUM((case when ShipMisc.MiscCode = 'CITY' then ShipMisc.MiscAmt else '0.00' end)) as [Calculated_City],
	
  [ShipHead].[ShipDate] as [ShipHead_ShipDate],
	[OrderHed].[OrderDate] as [OrderHed_OrderDate],
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
	[ShipDtl].[PackNum] as [ShipDtl_PackNum],
	[ShipDtl].[PackLine] as [ShipDtl_PackLine]
from Erp.ShipHead as ShipHead
inner join Erp.ShipDtl as ShipDtl on 
	ShipHead.Company = ShipDtl.Company
And
	ShipHead.PackNum = ShipDtl.PackNum

full outer join Erp.OrderDtl as OrderDtl on 
	ShipDtl.Company = OrderDtl.Company
And 
	ShipDtl.OrderNum = OrderDtl.OrderNum
And
	ShipDtl.OrderLine = OrderDtl.OrderLine

inner join Erp.OrderHed as OrderHed on 
	OrderDtl.Company = OrderHed.Company
And
	OrderDtl.OrderNum = OrderHed.OrderNum

inner join Erp.ShipMisc as ShipMisc on 
	ShipHead.Company = ShipMisc.Company
And
	ShipHead.PackNum = ShipMisc.PackNum

inner join Erp.Customer as Customer on 
	OrderHed.Company = Customer.Company
And
	OrderHed.BTCustNum = Customer.CustNum

 where OrderDtl.PartNum like  '2SET'
 group by [Customer].[CustID], [Customer].[Name], [OrderDtl].[PartNum], [OrderDtl].[LineDesc], [ShipHead].[ShipDate], 
 [OrderHed].[OrderDate], [OrderDtl].[OrderNum], [OrderDtl].[OrderLine], [ShipDtl].[PackNum], [ShipDtl].[PackLine]
1 Like
4

Just wondering if your shipments may/may not have multiple misc charge lines and of mixed types?
e.g.
ShipMisc Seq 1 = TAX
ShipMisc Seq 2 = SHIP
etc…

And if you need an outer join on the ShipMisc table?

5

Good point with the outer join. I tried with both and it seems like with the outer join, it grabs some blank misc charged lines; which may or may not be useful in the future.

Thank you all for the help and support.