Here’sAM_Test3.baq (17.3 KB)
an export of it if it helps
1 Like
Hi All
Ive been trying to get this to work for sometime, but I cant seem to eliminate erroneous ref designators. Most of them are correct, but several of the Material Part rows are not showing the correct # of ref designators. Im not a technical person so hoping for help in simple terms.
concatenate.xls (942 KB)
1 Like
Glad this was addresses. I was pulling my hair out.
1 Like
Hi All, I downloaded Jose’s CustOrd BAQ and it works great. What i want to do is get the same concatenated field for quotes. I created a new CustQuotes BAQ using theCustOrd BAQ as a template. however I get a SQL error when i test it.:
Severity: Error, Table: , Field: , RowID: , Text: Bad SQL statement.
Review the server event logs for details.
Query returned 0 row(s).
Query has no more records to return.
Query execution total time: 29.015 ms.
Server Event Log:
Log Name: Epicor App Server
Source: IceAppServer
Date: 2/24/2020 11:19:01 AM
Event ID: 0
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: xxxxxxxxxxx
Description:
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword ‘and’.
Incorrect syntax near ‘)’.
Incorrect syntax near ‘Customer’.
Incorrect syntax near the keyword ‘or’.
Incorrect syntax near ‘)’.
Incorrect syntax near ‘)’.
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)
I did not copy the entire event here. Can somebody point me in the right direction to be able to get the concatenated field for quotes for a customer?
1 Like
Can you copy out the SQL view and paste it here? Looks like you may be missing a couple of syntax selections in the GUI builder.
1 Like
Do you mean the Query Phrase on the BAQ?
select
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
(REPLACE(REPLACE(((select
(CAST ( QuoteHed.QuoteNum AS VARCHAR ) + '~') as [Calculated_QuoteNum]
from Erp.QuoteHed as QuoteHed
where QuoteHed.CustNum = Customer.CustNum FOR XML PATH(''))) , '</Calculated_QuoteNum>',''),'<Calculated_QuoteNum>','')) as [Calculated_Quotes]
from Erp.Customer as Customer
or a SQL view from SSMS?
1 Like
This code worked just fine in SSMS. I did not have to modify anything. Curious on why the error is listed like that. You do not have the work “and”. You do have a lot of “)” but it worked without issue. Which I could help you further. In this case I would normally store this in an external database on the same SQL server and then call it with the following table qualifiers “with (nolock)”. Then, you can use an External Connection to the view and retrieve the information this way. Wish I could help further, but like I said it works in SSMS.
1 Like
Thanks for your help. you’re right it does run in SSMS (I didn’t think to try it directly in SSMS) but errors out in BAQ designer. not having the word “AND” or “OR” in the query was puzzling me too. thanks again
1 Like
could you screenshot your BAQ calculated field statement ?
1 Like
Did you add your subquery 2 to your subquery1 ? QuoteHed table is not shown as available table…Just customer table shows in your list…
1 Like
No, I used a subquery criteria:
copying same quries as the CustOrders.baq exaple that Jose posted. (Which runs without errors)
1 Like
Hi @Agortman,
i don’t know why having two subqueries, -did not read the thread from the beginning-, however this is the code i use to concatenate rows values in one single field:
STRING_AGG( ISNULL(QuoteHed.QuoteNum, ' '), '~')
i have tested it this way
5 Likes
I’ll take a look at that. this query was built in BAQ Designer, bur errors out with “Bad SQL” Statement. However as Josh pointed out, the query runs without error in SSMS
1 Like
yes i know pal, this website i use verify it the same way, just make sure you allow for enough number of characters within the declared string variable, try it and let me know
1 Like
You didn’t copy and paste the FOR XML PATH('') did you? Sometimes the wrong tick mark can be used when copy pasting.
2 Likes
I did, I also tried entering it directly in the expression build window.
When in the expression build window the single ’ mark actually writes a double " quote mark.
tried that with a single " as well as double “” same errors.
1 Like
Just a quick update on this. I opened a case with Epicor, they duplicated the issue and converted it to a problem (PRB0221865) for resolution.
1 Like