BAQ Woes

Epicor ERP 10
,
1

I am tasked with creating a way to display information from JobHead, JobOper, JobOpDtl, and PartTran.

The user will select a Resource Group from a combo box (easy enough done), and then get a listing of all operations that are scheduled out between GETDATE() and 2 weeks. That is easy. The hard part comes into play when I need to get a COUNT of all PartTran against the part referenced in each op that is returned. (I also have 2 other nested queries for PartTran, but getting past 1 will allow me to do the other 2 with ease)

So I whipped up a fairly basic SQL query that works great in SQL Studio and I am trying to turn into a BAQ. However I have having issues on the “SELECT COUNT(*)” line when I try to set it as a calculated field.

SELECT
        JobOpDtl.ResourceGrpID,
        JobOper.JobNum,
        JobHead.PartNum,
        JobHead.PartDescription,
        JobOper.StartDate,
        JobOper.CommentText,
        (   SELECT
                    COUNT(*) AS [TransCount]
            FROM
                    Erp.PartTran
            WHERE
                    PartTran.PartNum = JobHead.PartNum)
        AS [TransCount]
FROM
        Erp.JobOper JobOper
    INNER JOIN
      Erp.JobHead
        ON JobOper.Company = JobHead.Company
          AND JobOper.JobNum = JobHead.JobNum
    INNER JOIN
      Erp.JobOpDtl JobOpDtl
        ON JobOper.Company = JobOpDtl.Company
          AND JobOper.JobNum = JobOpDtl.JobNum
          AND JobOper.AssemblySeq = JobOpDtl.AssemblySeq
          AND JobOper.OprSeq = JobOpDtl.OprSeq
WHERE
        DATEDIFF(WEEK, JobOper.StartDate, GETDATE()) BETWEEN 0 AND 2
        AND JobOpDtl.ResourceGrpID = '@ResGrpID_Param'
        AND JobHead.JobReleased = 1
ORDER BY
        JobOper.StartDate

When I try to add that “SELECT COUNT” from PartTran as a Calculated Field, I get the error below:

image
image918×598 38 KB

Now I get that the error is being thrown due to a possible unauthorized query being present. What I don’t get, however, is a potential workaround.

Can I do the PartTran column as a subquery and pass the PartNum value from the main query to it as a parameter? I know I could just run the PartTran query seperately and filter it after-the-fact, but that is a lot of overhead.

I also looked into do this as a UD, but as far as I can see, there is no way to combine multiple adapters for a query. (IE: PartTran and JobOp). If it was considered ‘kosher’ to do this via a customization where I query the DB directly, I would. But, that is frowned upon and I would like to do it in an approved fashion.

2

You can’t use the word SELECT In a BAQ, you need to use a SubSelect Subquery.

Move that Subselect to its own Subquery, then bring that whole Subquery into the calculated field {Subquery1}

2 Likes
3

It was the SubSelect that was throwing me for a loop…I searched the site here for that word and came across a video posted by @smason a few years back that walked me through exactly what I was needing.

Thanks @josecgomez and @smason!

4

@josecgomez, again, thank you for your help last week. That gave me the push in the right direction. I now have a fully functional query that runs when I copy/paste it into SSMS:

select 
	[JobOpDtl].[ResourceGrpID] as [JobOpDtl_ResourceGrpID],
	[JobOper].[JobNum] as [JobOper_JobNum],
	[JobHead].[PartNum] as [JobHead_PartNum],
	[JobHead].[PartDescription] as [JobHead_PartDescription],
	[JobOper].[StartDate] as [JobOper_StartDate],
	[JobOper].[CommentText] as [JobOper_CommentText],
	[TranDate].[Calculated_TranDate] as [Calculated_TranDate]
from Erp.JobOper as JobOper
inner join Erp.JobOpDtl as JobOpDtl on 
	JobOper.Company = JobOpDtl.Company
	and JobOper.JobNum = JobOpDtl.JobNum
	and JobOper.AssemblySeq = JobOpDtl.AssemblySeq
	and JobOper.OprSeq = JobOpDtl.OprSeq
	and ( JobOpDtl.ResourceGrpID = 'MILL'  )

inner join Erp.JobHead as JobHead on 
	JobHead.Company = JobOper.Company
	and JobHead.JobNum = JobOper.JobNum
	and ( JobHead.JobFirm = 1  )

inner join  (select 
	(max( PartTran1.TranDate )) as [Calculated_TranDate],
	[PartTran1].[PartNum] as [PartTran1_PartNum]
from Erp.PartTran as PartTran1
group by [PartTran1].[PartNum])  as TranDate on 
	JobHead.PartNum = TranDate.PartTran1_PartNum
	and ( TranDate.PartTran1_PartNum = JobHead.PartNum  )

inner join  (select 
	[PartTran].[PartNum] as [PartTran_PartNum],
	(COUNT(*)) as [Calculated_TransCount]
from Erp.PartTran as PartTran
group by [PartTran].[PartNum])  as TransCount on 
	JobHead.PartNum = TransCount.PartTran_PartNum
where (JobOper.StartDate >= GETDATE()  and JobOper.StartDate <= DATEADD (week, 2, GETDATE()))

The query itself is fairly straightforward, and when ran in SSMS it returns an accurate set of 26 results in less than a second. However, when I test it in the BAQ Designer, it times out.

Severity: Error, Table: , Field: , RowID: , Text: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Query returned 0 row(s).
Query has no more records to return.
Query execution total time: 120043.4965 ms.

I am sure that Epicor handles BAQs differently than just running straight SQL queries, however, is there enough of a difference in how a BAQ is ran that a 1 second SQL query would just time out when Epicor BAQ-ifies it?

5

BAQ engine has to add security rules to make query to return only current company rows.
And from what I see, it will not be happy at least with this part of your query:

When you add PartTran.PartNum = JobHead.PartNum, you should add Company columns as well
PartTran.Company = JobHead.Company AND PartTran.PartNum = JobHead.PartNum
This will allow SQL server to use existing indexes as well.

1 Like
6

@Olga - I took your advice and added Table.Company to the table relations linking the subqueries. Unfortunately, that did not make any change to the execution timeout of the query. I tested the new query in SSMS, and it returns the correct data in less than a second, while the BAQ still times out after 2 minutes.
I have played around with putting the criteria in the table relations, as well as in table criteria, nothing seems to have any affect on it. I’m at a complete loss.

7

Off the top of my head,
I’m suspicious of the calculated field “TransCount”
My experience has been that PartTran queries are a lot faster if you can include the index fields
e.g. I tend to use
one subquery to pull all parttrans for part AND include the pirmary index fields (reference the data dictionary)
then a second subquery to do my aggregation(s)

1 Like
8

I must be overlooking something, @bordway - I decided the count(*) wasn’t necessary, and nixed that from the BAQ, leaving only the TopLevel Query and the MAX(TranDate) subquery. In my mind one less subquery should help speed it up a little…

The table relations are on JobHead.Company = PartTran.Company and JobHead.PartNum = PartTran.PartNum. The query still times out.

There are no other key fields/index’s I can find that I would be able to use (I don’t think). If I try to link them with JobNum, the subquery will be restricted to that subset of data, which is not what I need.

I’ve tried adding Company and PartNum to the Table Criteria, which does nothing either. I am dumbfounded as to why something as simple as a “Get me the last transaction date for this part” is such a difficult task in a BAQ.

9

@jhecker Here are some tips from a thread about baq speed.

10

In SubQuery1 - PartTran table I usually include the index fields shown in the screenshot below.
In Subquery2 - Aggregate SubQuery1 values per the PartNum
Finally SubQuery3 - Join Subquery2 on PartNum to the desired table.field (JoHead.PartNum)
image

Also, have you been reworking the same BAQ? If so, might want to start from scratch.
Depending on the build order, some (bad) things get saved by the BAQ designer that don’t get cleared out with updates.

And finally, can you export/upload the latest copy of your BAQ here?
Thinking that might be easier than trying to decipher the topic notes for us now.