Con esto ya no es necesario utilizar el REPLACE
“FOR XML PATH(’’), TYPE).value(’.’, ‘varchar(max)’)”
SELECT p1.CategoryId,
stuff( (SELECT ‘,’+ProductName AS PRODUCTO
FROM Northwind.dbo.Products p2
WHERE p2.CategoryId = p1.CategoryId
ORDER BY ProductName
FOR XML PATH(’’), TYPE).value(’.’, ‘varchar(max)’)
,1,1,’’)
AS Products
FROM Northwind.dbo.Products p1
GROUP BY CategoryId ;
As this zombie thread still maintains its relevance, I am back yet again. We are validating 10.2.700.14 and the original queries are not working, including @josecgomez 's original example. The latest version of SQL server coupled with the epicor version calls various errors. The solution is to use the new STRING_AGG function which works very well and simplifies the original. Note this is not backwards compatible. A small note, if you use use STRING_AGG on a current version it may throw an error as it is limited to returning only 8kb. The solution is to cast it into nvarchar(MAX).
To be crystal clear, If your SQL version is not 2017+ you will not be able to use STRING_AGG
Attaching an updated CustOrder query modified by @LoganS
select
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
(STRING_AGG(cast(OrderHed.OrderNum as nvarchar(MAX)), ', ')) as [Calculated_Aggregate]
from Erp.Customer as Customer
inner join Erp.OrderHed as OrderHed on
Customer.Company = OrderHed.Company
and Customer.CustNum = OrderHed.CustNum
group by [Customer].[CustID],
[Customer].[Name]
CustOrder700.baq (23.7 KB)
Nice, I was playing about with this concept doing the same thing against Customer and orders table.
Went about it a slight different way as an experiment by putting the following custom code post Get List:
var s1 = Stopwatch.StartNew();
foreach (var ttResults_Row in ttResults)
{
StringBuilder builder = new StringBuilder();
int Orders_Count = (from row in Db.OrderHed where
row.Company == callContextClient.CurrentCompany &&
row.OpenOrder == true &&
row.CustNum == ttResults_Row.Customer_CustNum
select row).Count();
ttResults_Row.Calculated_OrderCnt = Orders_Count;
if (Orders_Count > 0)
{
foreach(var Orders_Row in (from row in Db.OrderHed where
row.Company == callContextClient.CurrentCompany &&
row.OpenOrder == true &&
row.CustNum == ttResults_Row.Customer_CustNum
select row))
builder.Append(Orders_Row.OrderNum.ToString()).Append(",");
ttResults_Row.Calculated_OrdersList = builder.ToString();
}
s1.Stop();
ttResults_Row.Calculated_eXeTime = Convert.ToInt32(s1.Elapsed.TotalMilliseconds);
}
BAQ made on version 10.1.400
ConcatV2.baq (27.9 KB)
For anyone that’s running 10.2.700+ on a pre-2017 version of SQL, you can still get the “FOR XML PATH” trick to work by adding the query execution setting (in BAQ Designer: Actions->Execution Settings) named queryOldCompanySecurity and set it to 1.
I believe this should work on versions 10.2.700.5+, and you can request a hotfix from Epicor for earlier versions. (See EpicCare technical support bulletin KB0104912)
This is the way.
I have everything working from this thread but wanted to know if anyone has figured out how to put a sort order on the subquery (or potentially within the calculated field). I am trying to list all of a Customer’s attributes (the Attribute Description more specifically) in alphabetical order. We are on 10.2.200.40 and when I try to put a sort on the subquery, I get the following error:
Errors in subqueries definition
Subquery CustomerAttributes can contain Order By clause only in CTE or inner subquery group and with TOP or OFFSET clause specified for it. To sort overall query results specify sorting in TopLevel subquery
Make your subquery a CTE, then you can add a sort into it.
I’m still getting the same error when trying to use a CTE:
Errors in subqueries definition
Subquery CustomerAttributes can contain Order By clause only in CTE or inner subquery group and with TOP or OFFSET clause specified for it. To sort overall query results specify sorting in TopLevel subquery
Can you see if this EpicCare article is helpful for you?
KB0039364 - Turn Multiple Row Values from a Query into a Comma Separated List
When I import this and run it, I get “Severity: Error, Text: Bad SQL statement.”
Do you know what version of SQL server are you running? Also what version level is your database set to?
I don’t know. How would I find that information?
New query sql
select @@version for the sql server…
The second part I am not sure…is it compatibility level ? (db properties/options )
The reason I ask is String_Agg is supported by, “SQL Server 2017 (14.x) and later”.
A cool feature of this function I just discovered - if you want to alphabetize your aggregate results, add “WITHIN GROUP (ORDER BY [field])” after calling STRING_AGG.
For example, to alphabetize and concatenate values from PriceLst with a comma, you’d use:
STRING_AGG(PriceLst.ListCode,',') WITHIN GROUP (ORDER BY PriceLst.ListCode)
Hi Jose, i’ve already trying your BAQ, but it turns out an error ‘A generic error occurred in GDI+.’ i couldn’t fix mw issue.
Need help, thank you.
That occurs when you try to bring too much data back to the screen, like a crap ton of large character fields.
Hello Kevin, Thanks for the answer, but i’ve already put the criteria for just 1 part. How can this error possible?
I don’t know, but that’s a local error, not one from the baq itself.
You can show us what you got.
