FOR XML PATH versus STRING_AGG

I just verified my fix is in 10.2.700.4. We tested that it works with Dashboards and BAQ Reports. If you see this issue anywhere else, let us know. :slight_smile:

1 Like

For the Cloud customers, the corrected elements will be installed as part of the 10.2.700.2 Upgrade.

1 Like

Can anyone tell me how to use STRING_AGG once I do have SQL2017+? As noted above, I am utilizing a subquery to concatenate the work centers for a job into a single line. I use the FOR XML PATH and REPLACE. How can I perform a similar function using STRING_AGG? Would I just add a calculated field with this formula?

STRING_AGG(JobOper.OpSeq,", ")

Thanks!
Nate

1 Like

That’s correct. Then you do everything else the same, except you don’t need to mess with the replace crap anymore.

2 Likes

Wow, that is easier than the old method! In my case I needed distinct so still used a SubQ to pull just distinct OrderNum values.

select 
	[InvcHead].[Company] as [InvcHead_Company],
	[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
	(String_Agg(OrderNums.InvcDtl1_OrderNum,', ')) as [Calculated_SONum]
from Erp.InvcHead as InvcHead
left outer join  (select distinct
	[InvcDtl1].[Company] as [InvcDtl1_Company],
	[InvcDtl1].[InvoiceNum] as [InvcDtl1_InvoiceNum],
	[InvcDtl1].[OrderNum] as [InvcDtl1_OrderNum]
from Erp.InvcDtl as InvcDtl1)  as OrderNums on 
	InvcHead.Company = OrderNums.InvcDtl1_Company
	and InvcHead.InvoiceNum = OrderNums.InvcDtl1_InvoiceNum
group by [InvcHead].[Company],
	[InvcHead].[InvoiceNum]

I think we have been updated so that we can use String AGG now. Where can I verify that this is true? I tried a BAQ utilizing string AGG and it popped an error. “Not a recognized built-in function name”

It should work in a BAQ, can you show your calculated field?

This one works.
image

 
select 
	[JobOper].[JobNum] as [JobOper_JobNum],
	(String_Agg(JobOper.OpSeq,", ")) as [Calculated_Ops]
from Erp.JobHead as JobHead
left outer join Erp.JobOper as JobOper on 
	JobHead.Company = JobOper.Company
	and JobHead.JobNum = JobOper.JobNum
where (JobHead.JobNum = '12345/1')
group by [JobOper].[JobNum]

I see the single quote you used. I also tried that and got the same error.

Is there no other definitive way to know if we have been updated?

It turns out that our Live environment was updated before our Pilot environment. So the code works great in Live, but not in Pilot. At least I know how to use the syntax now.

String_Agg(JobOper.OpSeq,', ')

This has to be a calculated value, and the other fields in the query must be set to ‘group by’.
Thank you all!
Nate

1 Like

Is there a way to sort the JobOper.OpSeq number ascending or descending? Mine are random.

You can add an order by after the string_agg() command

STRING_AGG(cast(convert(varchar, PartOpr.OprSeq) + ‘~’ + PartOpr.OpCode as nvarchar(MAX)), ’ / ’ )

This is my calculated field in my BAQ.
Adding a sort after this does not pass “Check Syntax” no matter how I phrase it.

The other thing you can try is add the sort order in the Display Fields sort order. Normally you can’t do that in a sub query, but since it’s a single sub select, you can. See if that works.

image

What is the error?
Because SSMS returns data for you expression without errors if I fix quotes:
select STRING_AGG(cast(convert(varchar, PartOpr.OprSeq) + ‘~’ + PartOpr.OpCode as nvarchar(MAX)), ’ / ’ )
from Erp.PartOpr

Severity: Error, Text: Bad SQL statement.
Review the server event logs for details.
Query returned 0 row(s).
Query has no more records to return.
Query execution total time: 45.1758 ms.

do you have access to server logs?

Sorry Between Public Cloud and BAQ limitations I’m feeling stuck.
I have even tried having my table link or table criteria point to the table index that could fix the issue but SQL doesn’t get my drift.

Let’s step back here. You said that the calculated field that you posted is working, right? It’s just the ordering that isn’t working?

Correct
60~Sort/30~Wash/20~Head/40~Roll/50~Plate/70~PackB003
I want to sort the operation sequence in order.