Alright, is the BAQ other than that relatively simple? I would like to recreate to see if I can get it to work. I feel like I’ve done this before.
I got it. Looks like you need “Within Group” added in there. Here is an example with job operations ascending and descending. Let me know if you can’t get this working.
Here’s where it is on the page in the docs.
And this is what you need for the partOpr table. I’m not sure what you were trying to do with the cast in there, but you don’t need it for this to work. The OpCode is already a string, so nothing to covert there.
String_Agg(convert(varchar, PartOpr.OprSeq) +'~' + PartOpr.OpCode,' /') Within group (order by PartOpr.OprSeq asc)
4 Likes
You were using the partOpr table. Are you using the JobOper tables now?
Testing both ways.
Part - PartRev - PartOpr
JobHead - JobAsmbl - JobOper
Sorry for the confusion.
what seems odd is that the “STRING_Agg” command runs fast yet seems to ignore the tables index order when displaying data.
Could adding the “Within group (order by joboper.OprSeq desc)” be blocked since I’m on epicor public cloud?
I’m not sure. I know this doesn’t help, but “It works for me”… It will probably take more research to figure out what the issue it. The SQL version will probably come into play here. I wish I had a more definitive answer.
Thanks for you help,
Notes for the record:
I have seen three methods for displaying routing records on a row all with issues.
PATH XML = doesn’t work
STRING_AGG function is fast but doesn’t sort at least for me in Public Cloud.
Creating a dozen CTE subqueries linked returning 1 row each, each looking for the next operation record greater than the previous = makes for a slow running baq
I still think there is a better way but SQL gives up it’s secrets slowly.
Is your subquery a regular innersubquery or a CTE? A CTE won’t work for this. (not sure why, but when I switch it to CTE is fails)
Incorrect syntax near ‘Within’.
Incorrect syntax near ‘Within’.
It works if I remove
Within group (order by PartOpr.OprSeq asc)
It’s gotta be a SQL version thing then. I just imported into our kinetic test system 2021.2.2 and it works fine, and it comes from 10.2.500 where it also works. We are on SQL version 2017 (I checked). You’ll have to figure out what they are running in cloud and see if it’s different.
Sorry, It runs if I remove the " Within group (order by PartOpr.OprSeq asc)" but it’s back to unsorted.
1 Like
SQL version issue or SQL compatibility mode issue is the best guess.
The cloud team told me SQL 2019
Thanks for the help Brandon. Much appreciated.
My test system is on 2019. I have no idea why this isn’t working for you.
We are on cloud dedicated Kinetic 2021.1.11, and I can confirm that this sample BAQ worked without syntax errors. I too was looking for a way to sort my aggregated strings. Thanks Brandon!