BAQ: Calculated Field Concatenate Rows to single field

recowed233 · March 18, 2020, 8:52pm

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.

recowed233 · April 15, 2020, 8:27pm

Sigo atento a una respuesta.
Gracias.

Banderson · April 15, 2020, 9:33pm

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.

Mike_Tonoyan · June 29, 2020, 9:37pm

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?

Banderson · June 30, 2020, 12:41pm

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.

Mike_Tonoyan · June 30, 2020, 9:08pm

Thanks Brandon, that did the trick.

Best regards.

Mike Tonoyan

miketonoyan@gmail.com

Cell.: 818-397-8515

lumanjon · July 23, 2020, 4:11pm

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?

Agortman · July 23, 2020, 4:19pm

They told me it would be fixed in 10.2.700 they did not give me a work-around.

Agortman · July 23, 2020, 4:57pm

Sorry, I didn’t refresh myself on the topic. I was able to use Jose’s BAQ for sales orders related to a customer. I modified the BAQ to get the quotes for a customer, that is where I was getting the SQL error. (which will reportedly be fixed in 10.2.700.)
My code indicates a space between the double “single quotes” like:

‘</Calculated_OrderNum>’, ‘ ’), ‘<Calculated_OrderNum>’, ‘ ’)) as [Calculated_Orders]…

lumanjon · July 23, 2020, 5:30pm

Added a space between the single quotes as suggested but still getting the same error.

ckrusen · July 23, 2020, 5:37pm

You didn’t just copy and paste from here did you? If a poster doesn’t use the proper method for posting code, single and double quotes get changed to “fancy quotes”

The following is unformatted
[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

and here it is with proper quotes

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

see how the first one changes to fancy quotes:

lumanjon · July 23, 2020, 5:54pm

Did not copy and paste. Seems to have something to do with the FOR XML PATH(’’) clause. If I remove this clause, the original error goes away. The BAQ will not work at that point because the subquery is not connected.

ckrusen · July 23, 2020, 6:00pm

~~The SubQuery Criteria’s expression should only include tables that are in the subquery.~~

~~Your Subquery2 only has OrderHed, while the criteria expression references Customer.CustNum~~

~~Try changing that to OrderHed.CustNum~~

edit

whoops … was way wrong on that…

ckrusen · July 23, 2020, 6:09pm

My sub query has a single column returned. A calc field OrderLine with the settings expression:

Note: OrderHed is not a table in this subquery. The subquery uses OrderDtl1 and OrderRel1 (aliases of OrderDtl and OrderRel)

The Sub Query Criteria has several rows, but you only need the last (and it must be the last)

The expression for that Filter Value is:

lumanjon · July 23, 2020, 6:43pm

Calvin, which version of Epicor you on?

lumanjon · July 23, 2020, 8:31pm

Here is the trace packet when the error is generated.

Ice.Proxy.BO.ReportMonitorImpl GetRowsKeepIdleTime ReportMonitorTableset 7/23/2020 15:25:58:6223427 PM 41 '') OR (PrintDriver='SSRS' AND (AutoAction = 'PREVIEW' OR AutoAction = 'PRINT') AND LastAction = 'SSRSREADY')) AND WorkStationID ='MS1204 1']]> Ice.Proxy.BO.SysMonitorTasksImpl GetRows SysMonitorTasksTableset 7/23/2020 15:25:58:6694856 PM 44 07/18/2020 and SubmitUser = 'jluman']]> Ice.Proxy.BO.DynamicQueryImpl Execute System.Data.DataSet 7/23/2020 15:26:05:0867094 PM 63 MSM jlumanTestConcat jluman Testing to combine rows into single field 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.OpenOrder = true and OrderHed.CustNum = Customer.CustNum FOR XML PATH(''))), '</Calculated_OrderNum>', ''), '<Calculated_OrderNum>', '')) as [Calculated_Orders] from Erp.Customer as Customer false false false 3.0.7.0 false false false 613497985 9a7dba07-67dc-45c3-92a3-4bf0350cd429 0 MSM jlumanTestConcat 31fb7cf6-c2fb-431b-9d7d-919cc6ee3f2e SubQuery1 TopLevel 1 false All 0 false false 613497986 f5ea8840-0567-499a-afd0-815e4564e8c0 0 MSM jlumanTestConcat 88f65d63-534d-42c5-95fb-305ae423482e SubQuery2 InnerSubQuery 2 false All 0 false false 613497987 9141728f-9ea1-42b4-8ac7-1e313aa34eec 0 MSM jlumanTestConcat 88f65d63-534d-42c5-95fb-305ae423482e OrderHed 64c4305c-6641-4078-b5e2-19c5d5f27890 1 OpenOrder bit = false true 0 bit true false 613497988 8f84b4fd-0548-4f51-8cfb-35c0fec53cdc 0 MSM jlumanTestConcat 88f65d63-534d-42c5-95fb-305ae423482e OrderHed c012342b-e4e1-4536-943a-a33e7708e53b 2 CustNum int = And false false 0 Customer.CustNum FOR XML PATH('') false 613497989 f0815073-7175-4868-92c1-6dc100b82f3a 0 MSM jlumanTestConcat 88f65d63-534d-42c5-95fb-305ae423482e Calculated -1 TT false false false 613497990 1e3d6206-5811-47eb-a4c6-ba22eb07a15b 0 MSM jlumanTestConcat 31fb7cf6-c2fb-431b-9d7d-919cc6ee3f2e Calculated -1 TT false false false 613497991 80e202e0-b8e6-4317-a582-3c5036aa2d0d 0 MSM jlumanTestConcat 31fb7cf6-c2fb-431b-9d7d-919cc6ee3f2e Customer 1 Erp Customer DB false true false 613497992 fe067d91-319e-40d5-b3f4-0b94cc1e78c3 0 MSM jlumanTestConcat 88f65d63-534d-42c5-95fb-305ae423482e OrderHed 1 Erp OrderHed DB false true false 613497993 a17dc823-83c3-4187-91c1-0da7c9ae7fb6 0 MSM jlumanTestConcat 31fb7cf6-c2fb-431b-9d7d-919cc6ee3f2e Customer CustID 1 Erp Customer CustID nvarchar A user defined external customer ID. This must be unique within the file. This ID may be used in certain screen displays or reports where a full customer name is inappropriate. Therefore users should use meaningful characters as they would in any other master file. This master file key is a little different in that the user can change. This change is allowed because the system is not using the CustID as a foreign key in any other file. Rather it uses the CustNum field which is assigned to the customer by the system. false false x(10) Cust. ID Customer 1 false false CustID false false false Customer_CustID 613497996 a53e9ed7-9909-43ac-ba3a-5b6f457d9203 Customer_CustID 0 MSM jlumanTestConcat 31fb7cf6-c2fb-431b-9d7d-919cc6ee3f2e Customer Name 2 Erp Customer Name nvarchar The full name of the customer. false false x(50) Name 2 false false false false false Customer_Name 613497997 769893e7-e4c5-4806-bf36-e8cd892f37fd Customer_Name 0 MSM jlumanTestConcat 31fb7cf6-c2fb-431b-9d7d-919cc6ee3f2e Calculated Orders 3 nvarchar false true REPLACE(REPLACE({SubQuery2}, '</Calculated_OrderNum>', ''), '<Calculated_OrderNum>', '') x(8000) Orders 0 false false false false false Calculated_Orders 613497995 dcb6920d-1000-46ab-adea-bc369be7caea Calculated_Orders 0 MSM jlumanTestConcat 88f65d63-534d-42c5-95fb-305ae423482e Calculated OrderNum 2 nvarchar false true CAST(OrderHed.OrderNum AS VARCHAR) + ',' x(8000) OrderNum 0 false false false false false Calculated_OrderNum 613497994 2eb9460b-6932-431c-9e50-28843806e382 Calculated_OrderNum 0 PageNum 1 00000000-0000-0000-0000-000000000000 A PageSize 0 00000000-0000-0000-0000-000000000000 A NeedTotal False 00000000-0000-0000-0000-000000000000 A TestExecutionToken 9606bb6d-dc72-4e7c-89bf-2264eff55a83 00000000-0000-0000-0000-000000000000 A

lumanjon · July 24, 2020, 11:53am

Does anyone have a working BAQ to concatenate rows to a single field working on 10.0.700.4 that they can upload?

Or know of a way other than the FOR XML PATH method to accomplish the same thing?

knash · July 24, 2020, 12:12pm

Is this a Report BAQ? or a normal BAQ? Have you tried to run the query in another query editor like Toad? Just to make sure the query works.

lumanjon · July 24, 2020, 12:21pm

It is a normal BAQ.jlumanTestConcat.baq (17.2 KB)
Never heard of Toad.

knash · July 24, 2020, 12:24pm

that’s ok. ill open the baq and take a peek