Can't get the top record in my BAQ

Epicor ERP 10
1

Hello, I need to get just the TOP record on my query, in this case I’m trying to get the next operation not completed. I’m choosing to return the TOP record on my sub query but for some reason I’m still getting all the operations open left on my results.

image
image794×459 21.6 KB

2

Besides checking the box next to TOP, did you change the default value of the number to 1?

3

If I change that to 1 it returns 0 rows. I just want operation 100 to be return. Thanks

4

JobOper needs to be in its own SubQuery

Edit

Hmmmm… I see what you mean. The “TOP” setting on the sub-query seems to affect the overall BAQ, and not just that subquery’s results

Edit #2

I think that putting TOP xx limits that subquery to xx rows. Not just xx rows for the matching table relationships.

5

What you can do is make a sub-query that returns the JobNUm and MIN(OprSeq). Make sure to Group on Company and JobNum. And add criteria to this subquery to limit it to the operations not complete

Edit

image

6

Still getting 0 rows as a result. This is supposed to be simple LOL just give me the top record. Here is the query if it helps.

select
[JobHead].[Company] as [JobHead_Company],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobHead].[JobNum] as [JobHead_JobNum],
[SubQuery2].[Calculated_NextOp] as [Calculated_NextOp]
from Erp.JobHead as JobHead
inner join (select top (1)
[JobOper].[Company] as [JobOper_Company],
[JobOper].[JobNum] as [JobOper_JobNum],
(min(JobOper.OprSeq)) as [Calculated_NextOp]
from Erp.JobOper as JobOper
where (JobOper.OpComplete = 0)
group by [JobOper].[Company],
[JobOper].[JobNum]) as SubQuery2 on
JobHead.Company = SubQuery2.JobOper_Company
and JobHead.JobNum = SubQuery2.JobOper_JobNum
where (JobHead.JobClosed = 0 and JobHead.JobComplete = 0 and JobHead.JobNum = ‘B016673-044-26’)
group by [JobHead].[Company],
[JobHead].[PartNum],
[JobHead].[JobNum],
[SubQuery2].[Calculated_NextOp]

7

I always use Row_number in a calculated field of a subquery, and calling it rank. I was as well, never successful at using top options…

ROW_NUMBER() OVER(PARTITION BY //here you put your group by fields// ORDER BY //here you put the order by fields// ASC))

Then add a condition on the subquery added to your top query, to only retrieve the rank = 1

Pierre

8

Pierre I’m getting this error on my Calculated Field. I don’t need the Partition by at this moment just Order by OperSeq so I can get the TOP 1 result. Any idea why I’m getting this error?

image
image920×606 30.7 KB

9

First off, in my examples, I’m using JobMtl wher you’re using JobOpr. Should work the same way.

  1. My BAQ has two subqueries. Both are set to return ALL records
    image

  2. The subqry JobMtl_sub, just has the JobMtl table, with criteria to only return the Mtls that are not issued complete
    image

  3. The subqry JobMtl_sub has columns
    image
    with the Calculated field being: MIN(JobMtl.MtlSeq)

  4. Top level subqry is:

    image
    image773×551 20.6 KB

with the relationship between the JobMtl table and subqry JobMtl_sub being:

image
image782×199 5.36 KB

Here’s the output. I get only 1 Mtl Record per Job.

image

Also, you sure JobNum B016673-044-26 is still open and incomplete?

10

This worked I think what I was missing was to relate the OpSeq from the JobHead to the Subquery. Thank you so much