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
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.
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.