Help with BAQ

Epicor ERP 10
,
1

I am building a dashboard that will show jobs that are ready to ship but not necessarily complete (still charging materials). The last operation (MAX OprSeq) has been completed more than 3 days ago. I have started developing a query in SQL, but need to move it into BAQ. I am stuck on how to get the SELECT MAX query designed and working in the BAQ designer. Any ideas would be appreciated.
SQL Query

SELECT DISTINCT T1.jobnum,
T3.payrolldate,
T3.opcode
FROM erp.joboper T1
LEFT OUTER JOIN erp.labordtl T3
ON T1.company = T3.company
AND T1.jobnum = T3.jobnum
AND T1.assemblyseq = T3.assemblyseq
AND T1.oprseq = T3.oprseq
WHERE T1.jobcomplete = 0
AND T1.assemblyseq = 0
AND T1.opcomplete = 1
AND T3.complete = 1
AND T3.payrolldate < Dateadd(day, -3, Getdate())
AND T1.oprseq = (SELECT Max(T2.oprseq)
FROM erp.joboper T2
WHERE T2.jobnum = T1.jobnum
AND T2.assemblyseq = 0)

2

Use a sub query

2 Likes
3

That is what I am trying to do, but not sure how. My options are to drop in the phrase build of the top level query using a join or using subquery criteria 'selected values or field from specified subquery but I would need a way to pass the JobNum from the top level query.

4
  1. Create a new subquery of type “Inner Sub Query”. It will default to being named Subquery2.
  2. In the query designer (of Subquery2), add the JobOper table (it will name it JobOper1)
  3. Set a table Criteria on JobOper1, of AssemblySeq = 0.
  4. In the select columns of Subquery2 2, add Company, and JobNum
  5. Create a calc field named MaxSeq with the expression MAX(JobOper1.OrpSeq)
  6. Mark the group by boxes of the other two columns (Company and JobNum)
  7. Select Subquery1, and add SubQuery2 to it.
  8. Join SubQuery2 to JobOper (Company and JobNum)

That should get you started

5

I usually do it the same way you do it when it comes to getting a MAX Value for joining purposes or display purposes (like Last Approved Revision). I typically have much more Sub-Queries bundled together, it makes it much easier.

But if your Query is as simple as it looks, Sub-Query will prob be the best.

But just if you are curious, how a Sub-Select works in BAQ here is more info:

The beauty is you don’t even have to display it to use it in a JOIN, let’s pretend you have SubQuery2 that is a TOP 1 and returns only 1 column. You can just enter it in the JOIN Criteria.

image

There is always a time and place for it, depending on complexity, and speed. Typically a Sub-Query (not a sub-select) will be faster, but if you are grouping stuff and have groups after groups after groups and wrapped into many queries, its tough to maintain.

2 Likes
6

Thanks for the links, that helpes find a solution. I did not know you could put a subquery name inside braces.