BAQ Concatenate Rows Columns to Field

Epicor ERP 10
1

I am trying to concatenate multiple rows to a single field in a query and am wondering how to do it in a BAQ.

Below are a couple of different ways I can do it in a SQL query.

Example 1:

Select distinct ST2.JobNum,
substring(
(
Select ‘,’+ ST1.OpCode AS [text()]
From Erp.JobOper ST1
Where ST1.JobNum = ST2.JobNum
ORDER BY ST1.JobNum
For XML PATH (’’)
), 2, 1000) [OpCode]
From Erp.JobOper ST2
WHERE ST2.JobNum = ‘007373’

Example 2:

DECLARE @Operations VARCHAR(8000)
SELECT @Operations = COALESCE(@Operations + ', ', ‘’) + Erp.JobOper.OpCode
FROM erp.JobOper
WHERE Erp.JobOper.OpCode IS NOT NULL AND Erp.JobOper.JobNum = ‘007373’ AND Erp.JobOper.OpComplete = 0

PRINT @Operations

Example 3:

SELECT OpCode + ‘, ’ AS ‘data()’
FROM Erp.JobOper
WHERE Erp.JobOper.JobNum = ‘007373’
FOR XML PATH(’’)

All of these give me what I need in the SQL management studio.

Has anybody made this work in a BAQ or have other advice on how to deploy this? My next thought is to create the entire thing in a stored procedure and use an external BAQ but am trying to prevent that if possible.

Thanks,
Ross

2

Check out this thread. There is an example BAQ that you can download to show you how it works.

1 Like
3

Great! Thanks so much. I will work with that.

I don’t know how I missed that in my search.

Ross

4

One of our IT guys created a function under the Aggregate functions in SQL DB called dbo.Agregate. When grouping, we call this function in our BAQ calculated field : dbo.Agregate(field to concatenate) same as if we would do a sum(field). The result is the calculated field having all the grouped fields values.

1 Like
5

I would love it if you have any more info on how to do that. That would definitely be handy.

6

USE [Pilot-DB]
GO

/****** Object: UserDefinedAggregate [dbo].[Concatenate] ******/
CREATE AGGREGATE [dbo].[Concatenate]
(@value nvarchar)
RETURNSnvarchar
EXTERNAL NAME [Polymos.SQL.StringUtils].[Concatenate]
GO

In the BAQ I call dbo.Concatenate(field)
The result is comma seperated info.

Pierre

1 Like
7

And a look forward to SQL 2017…

Mark W.

8

so that’s why that doesn’t work! I saw that and was trying it to no avail. Good thing my IT guy is getting documentation now to upgrade to 2017.

9

But wait for Epicor to certify first!

10

So I’m probably just not doing it right, but that script wouldn’t run for us.

11

Now I am totally confused because according to the 10.2 installation guide it says:

“Windows Server 2012 R2 with SQL Server 2014, 2016 or 2017.”

No mention of Server 2016 which I thought was certified for 10.1

12

I’m working with Epicor now on a new E10 install, and they recommended Windows server OS 2016. We are working on that rebuild now.

13

Sorry , I think it is normal…
That’s what CreateTo new query gave me. I was not the one who generated this and I am not that advanced into scripting with SQL …
like What EXTERNAL NAME does ? I think it is calling so external dll I am not aware of…

But glad to hear 2017 will support such feature!

14

Epicor Customer Service is sending me “the latest SQL version” so I guess we will see what they send. We have been running 10.1 on Server 2016 with no problems.

15

CS sent me SQL server 2016 so apparently that is the latest supported version.