BAQ Concatenated Rows to Single Field

[nfrancis (Nicholas Francis)]

I’m trying to concatenate reference designators of BOM materials to a single row. I found previous posts a couple years old with instructions and a sample BAQ for doing this process on customers and sales orders. When I write my BAQ and check the syntax it says it’s ok, but then I get a bad sql statement error when trying to run the baq. In the previous posts there was an example BAQ, and when I downloaded and installed the example I got the same error about a bad sql statement. Has something changed between 10.1 and Kinetic that would limit this functionality within BAQ?

Here is the query phrase code from the BAQ I’m trying to write:

select
[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].[AltMethod] as [PartMtl_AltMethod],
(REPLACE(REPLACE(((select
(cast (PartMtlRefDes.RefDes as varchar) + ’ , ‘) as [Calculated_RefDesAll]
from Erp.PartMtlRefDes as PartMtlRefDes
where (PartMtlRefDes.Company = PartMtl.Company and PartMtlRefDes.PartNum = PartMtl.PartNum and PartMtlRefDes.RevisionNum = PartMtl.RevisionNum and PartMtlRefDes.AltMethod = PartMtl.AltMethod and PartMtlRefDes.MtlSeq = PartMtl.MtlSeq and PartMtlRefDes.MtlPartNum = PartMtl.MtlPartNum FOR XML PATH (’’)))) , ‘</Calculated_RefDesAll>’,’’),’’,’’)) as [Calculated_RefDesignators]
from Erp.PartMtl as PartMtl

[jgiese.wci (Joshua Giese)]

What is the error detail for the bad statement? You will find that on the server in the event log.

[Olga (Olga Klimova)]

FOR XML PATH versus STRING_AGG

Hi Everyone! I hope you all are doing well. Today I am facing a problem in our Pilot environment of Epicor. BAQs that utilize the FOR XML PATH command, are now broken. A quick response from Epi techs indicates that this is a known issue, and since we don’t yet have SQL 2017 or higher we can’t use STRING_AGG either. Has anyone else come up against this issue recently? Is there a simple process for replacing FOR XML with STRNG_AGG? In our BAQs we create a subquery that returns a list of the val…

[Banderson (Brandon Anderson)]

I wouldn’t use the For XML Path function.

Instead, use

String_Agg(table.field, ',')

The second argument is your delimiter, in this case a comma. It works just like a regular aggregate function, like Sum() or Max() etc.

This is assuming that you are on the newish version of SQL

[nfrancis (Nicholas Francis)]

Perfect, the String-Agg function solved my issue!

Thanks all!

[Mike_Tonoyan]

Hello,
Do you have a BAQ that you can share? We have several BAQs which use the FOR XML Path expression.

[Banderson (Brandon Anderson)]

Is your version number in your profile correct? If if is, you probably can’t use is. What happens when you try to use it?

I would give you a BAQ, but my version is newer, so I don’t think you’ll be able to import it.

Post some screen shots of what you are trying to do, and I can see if you are doing something wrong.

[Mike_Tonoyan]

Hi Brandon,
Thank you for your reply.
We are upgrading from Epicor 10.1.400.14 to Kinetic 2022.1.8. I am getting an error message when running the BAQ with the FOR XML Path expression in Kinetic 2022.1.7. Currently we are running the upgrade conversions, it will take a while until I’ll be able to login to Kinetic. I will attach screenshots as soon as I can login.

[Banderson (Brandon Anderson)]

Sounds good. When you get there, the string_agg() function works just like any other aggregate field. (like sum() or Count() etc.). You just need to pass in a delimiter at a second argument.

[Mike_Tonoyan]

Thanks Brandon. I’ll try it as soon as I can log back in.

[kylepbsps (Kyle P)]

Very nice! I’ve been dreaming of this ability.

[Mike_Tonoyan]

Hi Brandon,
I tried it after Kinetic was upgraded and it worked. Thank you very much for your help.