At first glance, it looks like they were using the FOR_XML to associate the JobMtl with its associated JobOper and then summing up the number of JobMtl that was issued complete.
so what error do you have with STRING_AGG(JobOper.JobNum,", ") ?
It should work in that version unless you have some old compatibility level
1 Like
The compatibility level is set to 2019.
The error is the same error that I get when trying to use the FOR XML PATH:
Severity: Error, Text: Bad SQL statement.
Bad sql statement is written in the UI. It does not explain anything for security reasons.
Now you have to go server event viewer or Epicor server log and find the exact error there. Without it it is just guessing.
While you’re checking, what is the purpose of the BAQ?
If I recall correctly, the BAQ returns all operations for open jobs where material is issued complete (basically a work queue) and shows how many days late that job is.
Here is the full error from the Epicor server:
Ice.Common.EpicorServerException: Invalid column name ', '. —> System.Data.SqlClient.SqlException: Invalid column name ', '.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at Ice.Blaq.Execution.QueryExecutor.ExecuteAndFillDataSetInternal(IDbConnection dbconn, QueryInfo queryInfo, DataSet resultDataset, Action2 perfLogger) in C:\_releases\ICE\ICE3.2.700.34\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 118 at Ice.Blaq.Execution.QueryExecutor.<>c__DisplayClass3_0.<ExecuteAndFillDataSet>b__0(IDbConnection dbconn) in C:\_releases\ICE\ICE3.2.700.34\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 37 at Ice.Blaq.Execution.QueryExecutionHelper.DoJobWithObject[TObj](Func2 theJob) in C:_releases\ICE\ICE3.2.700.34\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutionHelper.cs:line 195
— End of inner exception stack trace —
Yeah, makes sense. This is the signature for string_agg
STRING_AGG ( expression, separator ) [ <order_clause> ]
<order_clause> ::=
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
so it’s expecting an expression or column name and it is blank here.
I’d be curious to see what you get if you just eliminate the FOR XML PATH(‘’) completely.
lol, my thought as well. It looks like it becomes something like ", " and does not work after that.
Not clear what it is supposed to achieve anyway
I mean, if it runs, that’s a start. If it gives the wrong data, then at least we can find a different WHERE clause to fix that. 
The syntax
FOR XML PATH('')
removes the out-level XML element. If this resolves to a single job number, then the WHERE clause is true. If it resolves to zero or 2+, it will not.
This is a presentation I did on “For XML” years ago. I’m supposed to be narrating, so I’m not sure if you’ll be able to follow everything, but it’s how I build the query.
The key takeaway is this. For_XML() is a function that’s used to shove all the rows into an XML text blob for something to read. So, it just takes your whole query (or subquery) and shoves it into one cell. It has all of the XML tags because that’s what it’s for. The rest of the “Complicated” stuff that we are doing is for string manipulation to get the XML junk out of the way.
there’s another slide with a video that’s too large to upload. But basically I add the joins in the subquery criteria for a single sub select.
2 Likes
String_Agg() is just an aggregate function. I works the same as sum(), but instead of adding numbers together, it’s tacking on string.
If this is our table:
| category | value |
|---|---|
| a | 1 |
| a | 2 |
| a | 3 |
| b | 4 |
| b | 5 |
| b | 6 |
| c | 7 |
| c | 8 |
| c | 9 |
and we do Sum(value) group by Category
we get (the action colum is just showing you want it’s doing)
| Cat | Sum | (action) |
|---|---|---|
| a | 6 | 1+2+3 |
| b | 15 | 4+5+6 |
| c | 24 | 7+8+9 |
if we do String_agg(value,‘,’) group by Category we get:
| Cat | Sum | (action) |
|---|---|---|
| a | 1,2,3 | 1+2+3 |
| b | 4,5,6 | 4+5+6 |
| c | 7,8,9 | 7+8+9 |
It’s that simple. ForXML seems complicated, because of the string manipulation.
2 Likes
Thank you for all of that Brian, that is helping me start to understand what is going on here. Here is my best explanation:
There is a sub-query with filter criteria
JobOper.JobNum FOR XML PATH(‘’)
So based on your explanation, this is intended to generate a string of concatenated values with a comma in-between, but with all of that XML stuff in there too.
Then the only displayed value for Subquery2 is a calculated field of:
(case when sum(convert(int,JobMtl.IssuedComplete)) = count(JobMtl.MtlSeq) then 1 else 0 end)
Then in the BAQ’s primary query, there is a calculated field named ALLIssued whose expression is:
substring({subquery2},23,1)
My best guess of that calculated field is that it’s pulling out a specific value (the 23rd character) because the rest of it is the XML mumbo-jumbo?
So the first thing I did was just remove the sub-query filter criteria (the stuff with the FOR XML) and change the calculated field to be just {subquery2}. This returns 4200 rows so the query no longer errors out, but I am going to assume it is returning too many results because filter criteria that was previously there is now gone.
So my next step was to try replacing the Subquery2 filter criteria
JobOper.JobNum FOR XML PATH(‘’)
with
STRING_AGG(JobOper.JobNum,",")
or
STRING_AGG(JobOper.JobNum,',')
but both of those error out with the error below. Any pointers for next steps? This is an interesting problem.
Ice.Common.EpicorServerException: Column 'Erp.JobOper.DueDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. ---> System.Data.SqlClient.SqlException: Column 'Erp.JobOper.DueDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at Ice.Blaq.Execution.QueryExecutor.ExecuteAndFillDataSetInternal(IDbConnection dbconn, QueryInfo queryInfo, DataSet resultDataset, Action`2 perfLogger) in C:\_releases\ICE\ICE3.2.700.34\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 118
at Ice.Blaq.Execution.QueryExecutor.<>c__DisplayClass3_0.<ExecuteAndFillDataSet>b__0(IDbConnection dbconn) in C:\_releases\ICE\ICE3.2.700.34\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutor.cs:line 37
at Ice.Blaq.Execution.QueryExecutionHelper.DoJobWithObject[TObj](Func`2 theJob) in C:\_releases\ICE\ICE3.2.700.34\Source\Server\Internal\Lib\Ice.Lib.Blaq\Execution\QueryExecutionHelper.cs:line 195
--- End of inner exception stack trace ---
Just get rid of the for_xml altogether. You don’t need it. Put the where back to a normal table filter.
On second thought:
Start with another (brand new test) query so that you understand how the string agg works. Do you have any user codes in your sysem? Make a query on the UDcode table. Display the CodeTypeID and group by that field. Then make a calculated field with string_agg(CodeID,‘,’).
Once you get that to work in your test query come back and we’ll go from there.
I misread what you were saying. But still, try it out on a simpler BAQ first.
So if I change the Subquery2 filter criteria from:
JobOper.JobNum FOR XML PATH(‘’)
to just be:
JobOper.JobNum
the query runs without errors. So you’re thinking that FOR XML PATH(‘’) actually served no real function in the first place?
I don’t know if it ever worked if that’s the case. If it was implemented correctly before, you would have to bring it in as something called a single sub select. If you have more than one row coming back, it should puke on you. For_XML forces all of the rows into a single row.
Do you know what the BAQ results are supposed to look like? Just because it’s not erroring out doesn’t mean it’s working as intended.
Edit: And yeah, looking at this, you already have a sum operation in there anyways, so I don’t know what you would be returning with the for_xml().
“I don’t know if it ever worked if that’s the case”
Perhaps that is the case. I say that because just now I spun up a test environment with the SQL compatibility level set to 2016. The same query fails there. I would expect it to be ok on SQL 2016 if that was the root issue here.
There are two versions of this BAQ and one works and this one doesn’t. The broken one is the one that is used by the dashboard so I assumed it was working before, but maybe it never did and I’m just banging my head on the wall? 
I’ll remove the FOR XML and ask the end users to verify it is returning the results that the expect.
Here’s a BAQ that just returns released and not-completed jobs where all items have been issued. You can replace the SubQuery2 with this and link it to the JobHead in SubQuery1. This eliminates the whole XML FOR PATH issue. Also, remove the substring Calculated variable in the top query since you won’t need it anymore.
AllIssued.baq (16.2 KB)
select
[JobHead].[Company] as [JobHead_Company],
[JobHead].[JobNum] as [JobHead_JobNum],
(SUM (case when JobMtl.IssuedComplete = 1 then 0 else 1 end)) as [Calculated_OpenMtl]
from Erp.JobHead as JobHead
inner join Erp.JobMtl as JobMtl on
JobHead.Company = JobMtl.Company
and JobHead.JobNum = JobMtl.JobNum
where (JobHead.JobComplete = false and JobHead.JobReleased = true)
group by [JobHead].[Company],
[JobHead].[JobNum]
having (SUM (case when JobMtl.IssuedComplete = 1 then 0 else 1 end)) = 0
