How do you do a distinct concatenation in a baq? I’m currently concatenating with string_agg function.
What do you mean by a distinct concatenation? StringAgg works great for concatenating fields into a single field.
I’m getting repeats of the same value in the concatenated field. I only want to see it once.
You can go to subquery options for your top-level query and apply the Distinct option to it. However, I have learned the hard way that this is a hack approach. It does work, and if speed isn’t an issue, then just use it. As other have told me, it is best to find the source of the duplicates and try to eliminate them before returning records to the top level. Only do the Distinct at the top level if you can’t get rid of duplicates any other way.
Your subquery (the one that you are using sting agg on) needs to return a unique set of records. Set the subquery to the top query and refine it until it returns the correct results. Then when you move it back to a subquery, it will return the correct results into your concatenated field.
2 Likes
string_agg function is currently in epicor Ver 10.2.3 I can’t use it
This is the way i usually use to concatenate strings based on this article
FOR XML PATH versus STRING_AGG
this is my BAQ to get the number of Receipts by month
DMS_GetPOReceipt_SumPart.baq (59.3 KB)
Thank you very much! Have a good day! 
It looks like your duplicates are coming in from the first CTE, QtyRecepitDetail. If the same part is received on more than one packing slip on the same year/month, you get a duplicate. Put the packing slip number and pack line number into your report to see the source of the duplicates. Then decide how you want it to deal with those duplicates.
Good luck!
1 Like
My BAQ is doing the right thing, I’m just giving an example for using FOR XML PATH instead of STRING_AGG for concatenation.
because I do sum by part The monthReceiptList column is for me to check which Part has 2 consecutive months without inventory when the code is on customization
Thank you very much! Have a good day!
So what is your question?
I didn’t ask but gave an example of concatenation for @jdlester
But for Distinct concatenation in baq, I just need to find the SubQuery or (CTE) Distinct sentence before the column data needs concatenation before concatenating, but in my epicor, the string_agg function is not usable, it can only be used on SQL
Thank you very much! Have a good day!
1 Like