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.
For the Cloud customers, the corrected elements will be installed as part of the 10.2.700.2 Upgrade.
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?
That’s correct. Then you do everything else the same, except you don’t need to mess with the replace crap anymore.
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.
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.
This has to be a calculated value, and the other fields in the query must be set to ‘group by’.
Thank you all!
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.
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)), ’ / ’ )
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?
I want to sort the operation sequence in order.