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 stat…

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

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

[image] FOR XML PATH versus STRING_AGG ERP 10 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 k…

Perfect, the String-Agg function solved my issue! Thanks all!

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

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 doi…

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 …

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. [image] [image] [image]

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

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

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

BAQ Concatenated Rows to Single Field

Kinetic ERP

Olga (Olga Klimova) June 1, 2022, 7:17pm 3

Topic		Replies	Views
FOR XML PATH versus STRING_AGG Epicor ERP 10 baq	66	14439	October 28, 2024
Error while using FOR XML PATH statement Kinetic ERP baq	5	1282	April 25, 2023
FOR XML in BAQ Epicor ERP 10 e10 , baq	33	5072	October 14, 2022
BAQ - Concatenate results from subquery into one field Epicor ERP 10 baq	7	3028	August 30, 2023
BAQ Concatenate Rows Columns to Field Epicor ERP 10	14	10689	April 30, 2018

BAQ Concatenated Rows to Single Field

Related topics