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
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.
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
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)), ’ / ’ )
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.
So on the same sub query that this calculated field is in, can you go to the fields tab, then sort, and add the op seq in there? In the place I showed the screen shot earlier.
Query Execution Message
Column “Erp.PartOpr.OprSeq” is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.