"Merge" Duplicate Rows or Remove them in Recursive BAQ

Epicor ERP 10
1

Hello All!

I am finishing up some tweaks with my Indented BOM BAQ, and one of the things pointed out to me is that we are returning multiple rows for parts when there is more than one supplier part number. I understand why this is happening, but we don’t want it to. I wanted to know if it was possible to actually “merge” duplicates with commas separating the two results? Everything would be unique except the one column. If not, I would just like to return the first result. I did some reading up on it, and nothing I seem to do allows me to just grab the first result.

Do I need to create another subquery that includes only that column, and then use “Distinct” on my SubQuery options?

image
image1206×213 8.34 KB

2

Have you already seen this?

3

@bordway I just took a look at this now. It looks like it may be a step in the right direction, but it is very confusing. Also, since my query is recursive, I took most of my tables out of my subquery and moved them to my toplevel query, and the specific item I need to return one row only for, ‘PartXRefVend_VendPartNum’ causes issues if it is on my subquery, so not sure how I would go about implementing that.

4

On your top level, group by all of the fields, then add a calculated fied

String_Agg(PartXRefVend.VendPartNum, ' ,')

This will take all of the vendor part numbers and put them in one cell, separated by a comma.

Don’t add any fields from that table directly, or you will get duplicate rows again.

1 Like
5

Does this include the calculated fields? And the one you’re having me add?

6

It includes any calculated fields that you already have, but not the one that I’m having you add.

If you check the syntax, it will tell you any fields that are a problem.

7

@Banderson thanks this seems to work! Now, how do I make it so that it won’t show a comma in an empty row? Just do a case when VendPartNum = “”?

Also, I have two calculated sorts that won’t work anymore once I’ve moved most of my fields to my top level query, saying that they are not part of an aggregate function or order by clause. Any idea about this? If not I can look more into it

8

You should only have that if you have rows coming back with no vendor part number. You can filter them out of the table if you don’t want them to show up.

9

@Banderson to explain it better, I am using both VendPartNum and MfgPartNum, both of which could be empty for a given part, or have multiples. Here is an example of what I see, and FYI this is after creating two calculated fields, one for each with the code you sent.

image

1 Like
10

Have you tried setting the BAQ to distinct?

11

I’m trying to think of how to do it. The only thing I can think of at the moment is to bring the table in twice, then filter each table based on what you are looking for. Otherwise, you probably could so some string manipulation in a case statement.

case when (calculated stuff) = ',' then ''
else (calculated stuff)
end

But I don’t know how well that’s going to play with the grouping part.

12

@Banderson seems to play well enough, and removes the comma on empty. However, how do I stop from showing all four calculated fields?

image

13

What do you mean? If it’s in the BAQ, who cares? Just don’t display it on the dashboard.

Or put your calculated stuff in the actual calculation, don’t reference it outside of the field like that.

14

True lol, I will remove that at load time. On another note, for some reason my calculated field sorts no longer work, but I think this was after I moved some tables to my toplevel query. Any ideas?

image

If I add Ind1 to the display field and group it, it goes through fine…

image

Although, it seems like my first row in my query is getting thrown at the bottom?

image
image1583×195 9.5 KB

15

Just wanted to throw this back out there to see if anyone knew why this was happening?

16

You’ll probably have to post the query for anyone to be able to tell you what’s wrong. Usually that error is because your sort isn’t in the query select.

17

No problem! My bad…

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
with [SubQuery1] as 
(select 
	[PartMtl].[Company] as [PartMtl_Company],
	[PartMtl].[PartNum] as [PartMtl_PartNum],
	[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
	[PartMtl].[MtlSeq] as [PartMtl_MtlSeq],
	[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
	[PartMtl].[QtyPer] as [PartMtl_QtyPer],
	[PartMtl].[RelatedOperation] as [PartMtl_RelatedOperation],
	(1) as [Calculated_Hierarchy],
	(case
   when PartMtl.MtlSeq < 10 then cast(concat('000', PartMtl.MtlSeq) as nvarchar(25))
   when PartMtl.MtlSeq < 100 then cast(concat('00', PartMtl.MtlSeq) as nvarchar (25))
   when PartMtl.MtlSeq < 1000 then cast(concat('0', PartMtl.MtlSeq) as nvarchar (25))
 end) as [Calculated_Ind1],
	(PartMtl.PartNum) as [Calculated_TopLevel],
	(case
   when Hierarchy = 1 AND MinMtlSeq1 = 1 THEN PartMtl.PartNum
   else ''
 end) as [Calculated_DisplayedTopLevel],
	(PartCost.StdLaborCost+ PartCost.StdBurdenCost+ PartCost.StdMaterialCost+ PartCost.StdSubContCost+ PartCost.StdMtlBurCost) as [Calculated_StdTotalCost],
	(Row_Number() over (partition by Part.Company, TopLevel order by PartMtl.MtlSeq)) as [Calculated_MinMtlSeq1],
	[Part].[UnitPrice] as [Part_UnitPrice],
	[Part].[PartDescription] as [Part_PartDescription],
	[PlantWhse].[PrimBin] as [PlantWhse_PrimBin]
from Erp.PartMtl as PartMtl
inner join Erp.PartCost as PartCost on 
	PartMtl.MtlPartNum = PartCost.PartNum
inner join Erp.PlantWhse as PlantWhse on 
	PartMtl.MtlPartNum = PlantWhse.PartNum
inner join Erp.Part as Part on 
	PlantWhse.Company = Part.Company
	and PlantWhse.PartNum = Part.PartNum
where PartMtl.PartNum like @PartNum

union all
select 
	[PartMtl1].[Company] as [PartMtl1_Company],
	[PartMtl1].[PartNum] as [PartMtl1_PartNum],
	[PartMtl1].[RevisionNum] as [PartMtl1_RevisionNum],
	[PartMtl1].[MtlSeq] as [PartMtl1_MtlSeq],
	[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
	[PartMtl1].[QtyPer] as [PartMtl1_QtyPer],
	[PartMtl1].[RelatedOperation] as [PartMtl1_RelatedOperation],
	(Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
	(case
   when PartMtl1.MtlSeq < 10 then cast(concat(Calculated_Ind1, '-', '000', PartMtl1.MtlSeq) as nvarchar(25))
   when PartMtl1.MtlSeq < 100 then cast(concat(Calculated_Ind1, '-', '00', PartMtl1.MtlSeq) as nvarchar(25))
   when PartMtl1.MtlSeq < 1000 then cast(concat(Calculated_Ind1, '-', '0', PartMtl1.MtlSeq) as nvarchar(25))
 end) as [Calculated_Ind2],
	(Calculated_TopLevel) as [Calculated_TopLevel1],
	(case
   when Hierarchy2 = 1 AND MinMtlSeq2 = 1 THEN PartMtl1.PartNum
   else ''
 end) as [Calculated_DisplayTopLevel2],
	(PartCost1.StdLaborCost+ PartCost1.StdBurdenCost+ PartCost1.StdMaterialCost+ PartCost1.StdSubContCost+ PartCost1.StdMtlBurCost) as [Calculated_StdTotalCost1],
	(Row_Number() over (partition by Part1.Company, TopLevel1 order by PartMtl1.MtlSeq)) as [Calculated_MinMtlSeq2],
	[Part1].[UnitPrice] as [Part1_UnitPrice],
	[Part1].[PartDescription] as [Part1_PartDescription],
	[PlantWhse1].[PrimBin] as [PlantWhse1_PrimBin]
from Erp.PartMtl as PartMtl1
inner join  SubQuery1  as SubQuery1 on 
	PartMtl1.PartNum = SubQuery1.PartMtl_MtlPartNum
	and PartMtl1.Company = SubQuery1.PartMtl_Company
inner join Erp.PartCost as PartCost1 on 
	PartCost1.PartNum = PartMtl1.MtlPartNum)

select 
	[SubQuery11].[Calculated_DisplayedTopLevel] as [Calculated_DisplayedTopLevel],
	[SubQuery11].[Calculated_Hierarchy] as [Calculated_Hierarchy],
	[SubQuery11].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
	[SubQuery11].[Part_PartDescription] as [Part_PartDescription],
	[SubQuery11].[PartMtl_QtyPer] as [PartMtl_QtyPer],
	[SubQuery11].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
	[PartWhse].[OnHandQty] as [PartWhse_OnHandQty],
	[PartWhse].[DemandQty] as [PartWhse_DemandQty],
	[SubQuery11].[Part_UnitPrice] as [Part_UnitPrice],
	[SubQuery11].[Calculated_StdTotalCost] as [Calculated_StdTotalCost],
	[SubQuery11].[PlantWhse_PrimBin] as [PlantWhse_PrimBin],
	(case when (VendPartNum) = ',' then ''
 else (VendPartNum)
 end) as [Calculated_VendorPartNum],
	(case when (MfgPartNum) = ',' then ''
 else (MfgPartNum)
 end) as [Calculated_MnfgPartNum],
	(String_Agg(PartXRefVend.VendPartNum, ', ')) as [Calculated_VendPartNum],
	(String_Agg(PartXRefVend.MfgPartNum, ', ')) as [Calculated_MfgPartNum],
	[SubQuery11].[Calculated_Ind1] as [Calculated_Ind1]
from  SubQuery1  as SubQuery11
left outer join Erp.PartXRefVend as PartXRefVend on 
	SubQuery11.PartMtl_MtlPartNum = PartXRefVend.PartNum
left outer join Erp.PartWhse as PartWhse on 
	SubQuery11.PartMtl_MtlPartNum = PartWhse.PartNum
group by [SubQuery11].[Calculated_DisplayedTopLevel],
	[SubQuery11].[Calculated_Hierarchy],
	[SubQuery11].[PartMtl_MtlPartNum],
	[SubQuery11].[Part_PartDescription],
	[SubQuery11].[PartMtl_QtyPer],
	[SubQuery11].[PartMtl_MtlSeq],
	[PartWhse].[OnHandQty],
	[PartWhse].[DemandQty],
	[SubQuery11].[Part_UnitPrice],
	[SubQuery11].[Calculated_StdTotalCost],
	[SubQuery11].[PlantWhse_PrimBin],
	[SubQuery11].[Calculated_Ind1]
order by SubQuery11.Calculated_DisplayedTopLevel, SubQuery11.Calculated_Ind1

And FYI I want it to sort by Calculated_DisplayedTopLevel, and then Calculated_Ind1

18

You could create your Part List with calculated VendPart and MfgPartNums from PartXRefVend and PartWhse as a subquery, then you wouldn’t have to do the group by on the main.

19

I can’t have those on a subquery because it won’t let me use an outer join on the recursive part of a query, so it won’t return all the results I need

20

Not on the CTE portion, on the main portion of the BAQ.

No different than adding the PartXRefVend and PartWhse tables, which is causing the “duplicates”.

You would create the subqueries and left join the query to SubQuery11.PartMtl_MtlPartNum