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
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
Make sure you are converting the QuoteNum to a navchar first?
1 Like
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
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
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
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
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)
6 Likes
This is the way.
2 Likes
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
Make your subquery a CTE, then you can add a sort into it.
1 Like
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
1 Like
Can you see if this EpicCare article is helpful for you?
KB0039364 - Turn Multiple Row Values from a Query into a Comma Separated List
3 Likes
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”.
3 Likes
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)
1 Like