BAQ: Calculated Field Concatenate Rows to single field

Experts' Corner
102

I opened the baq and it works in 10.2.200. It might be the version of Epicor you are on, sorry.

1 Like
103

Update:
I tested @lumanjon BAQ that works in 10.0.700.4 as well as @josecgomez BAQ CustOrder example on 10.2.700.2 and I also get Bad SQL Statement. Apparently this is not fixed yet as @Agortman had reported to Epicor Support.
I also tried the STRING_AGG() approach on a Cloud install of 10.2.700.2 so not sure what version of SQL is being used on the backend for Cloud but either it’s not SQL 2017 or it’s blocked or it’s some other related bug.

1 Like
104

Rick, Thanks for testing that. I was planning to test this shortly, however I discovered i need to upgrade to SQL 2017 first so that has delayed my test plan. Saddened that the issue is not fixed as they reported that it would be.

1 Like
105

Looks like your PRB has been rejected and they were able to get your BAQ to work with a change. So you might want to test your BAQ. If you can get it to work, I would like to try your BAQ that you get to work as apparently we are doing something wrong. I’ve done this before and I’ve been able to get it to work, so something may have changed. Check out their notes on your PRB:
https://epicorcs.service-now.com/epiccare?id=form&sys_id=2eaa96721b9b4cd08b19fdd51a4bcb90&table=problem&view=csp

1 Like
106

I see their notes now. However, i dont recall getting their revised BAQ. I do recall the accept or reject email, (but no attachment). I will play around with the sub-query (which is what they said they changed)

1 Like
107

I recalled they did accept it, then later rejected it without explaining what the “change” was. The interesting part is the BAQ works as expected on the order related tables, but not on the quote related tables.

1 Like
108

Done more testing and either it’s a bug in 10.2.700 or it’s a cloud vs on prem limitation.
Here is my test:
Created attached BAQ in 10.2.700.2 (cloud) referencing a similar working BAQ built in 10.2.600.8 (on prem).

Attempted to run the BAQ in 10.2.700.2 (cloud) and get:

Severity: Error, Table: , Field: , RowID: , Text: Bad SQL statement.

I then exported the BAQ built in 10.2.700.2 (cloud) and import it into the 10.2.600.8 (on prem) environment and the BAQ runs and returns expected BAQ results.

@Agortman are you still on prem or did you move to cloud and have issues?
It may have actually broke on 10.2.600.9… as mine works on prem at 10.2.600.8

1 Like
109

We are on premise. We were on 10.2.300.15 when we had the issue, we are now on 10.2.600.9 and that BAQ still does not work.

1 Like
110

Odd, it should work in 10.2.300. It works for a 10.2.600.8 client.

1 Like
111

What I think is odd is that the exact BAQ for sales orders works fine, but when I build the BAQ for quotes, it throws the bad SQL error. When I say exact, all settings and calculations are the same except it concatenates the quote numbers instead of the sales order numbers.

1 Like
112

Andrew, more information… sounds like what I am seeing is more of a clould issue, but it sounds like it can somehow be dependent on SQL configuration:

…there is currently an issue with the FOR XML PATH in 10.2.700. The issue is that 700 uses SQL Server 2017 or above while not all cloud servers have that yet, and in SQL 2017 there is a new more efficient way to do exactly that and it should work once you have SQL 2017 or above. I have attached the problem to the case if you want to see the details (PRB0227522) and it has already been resolved for 10.2.700.4 and even the For XML path should work in that version onward

What is odd is that he says that FOR XML doesn’t work on some versions of SQL, but I have not been able to determine, what version that would be. I think I’ve been using this as far back as MSSQL 2008. While I don’t know of any type of configuration that would block it, there might be a configuration issue with your SQL? What verison of SQL are you running?

1 Like
113

Make sure you are converting the QuoteNum to a navchar first?

1 Like
114

I was able to import your BAQ and it is completely identical to the one that @josecgomez posted. I am still getting the error:

Severity: Error, Table: , Field: , RowID: , Text: No column name was specified for column 1 of ‘SubQuery2’.

Has this issue been solved?

1 Like
115

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 ;

1 Like
116

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)

8 Likes
117

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)

1 Like
118

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.

image

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)

6 Likes
119

This is the way.

2 Likes
120

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

1 Like
121

Make your subquery a CTE, then you can add a sort into it.

1 Like