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
Hola. Buenas tardes.
Tengo una pregunta,
Realicé una BAQ similar a la presentada en el caso, pero tengo un requerimiento adicional, el cual es ordenar los elementos del Subquery de forma alfabetica ascendentemente.
Actualmente muestra los elementos de la siguiente forma:
Pero deberia ordenarlo de forma alfabetica así AMC, PLA
Intenté solucionarlo con un Subquery con la opción Sort Order, pero me muestra el siguiente error:
“Subquery Filtro 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”
Alguien sabe como puedo ordenar los elementos en el Subquery para llevarlo al Principal (Top Level)?
Gracias.
1 Like
Sigo atento a una respuesta.
Gracias.
1 Like
Where did you put the sort @recowed233? I think you should be able to put a sort in the subquery that you are getting the concatenated data from.
2 Likes
Hello, these were very helpful posts. I was able to modify a BAQ to show all CustID’s for customer part numbers. But since we have multiple companies the CustID field was populating with all CustID from every company.
Is there a way to link the Customer.Company to CustXPrt.Company AND SubQuery criteria Customer.CustNum = CustXPrt.CustNum FOR XML PATH(’’) without errors?
1 Like
Yes, you just need to make sure that the for XML path() part is added onto the last criteria. Make all the links the way would would without the for XML path() except for the last one.
1 Like
Thanks Brandon, that did the trick.
Best regards.
Mike Tonoyan
Cell.: 818-397-8515
2 Likes
Getting this same error and have not found a solution yet. Using 10.0.700.4.
Here is the error;
Severity: Error, Table: , Field: , RowID: , Text: No column name was specified for column 1 of ‘SubQuery2’.
Here is the Query Phrase;
select
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
(REPLACE(REPLACE(((select
(CAST(OrderHed.OrderNum AS VARCHAR) + ‘,’) as [Calculated_OrderNum]
from Erp.OrderHed as OrderHed
where OrderHed.CustNum = Customer.CustNum FOR XML PATH(’’))), ‘</Calculated_OrderNum>’, ‘’), ‘<Calculated_OrderNum>’, ‘’)) as [Calculated_Orders]
from Erp.Customer as Customer
Anyone know how to resolve this?
1 Like
They told me it would be fixed in 10.2.700 they did not give me a work-around.
1 Like