Next Operation

Hi,

I am trying to use an Epicor BAQ to display the next operation for all operations in the list. The query works great for that. But when I add in the information to also return the next resource group, I begin to get multiple records. I believe that means I am missing something in my join, but I cannot seem to figure out what to do. Below is the code from the BAQ and I have attached the BAQ if anyone would like to load it. It is built in 10.2.400.14. Any help / direction is much appreciated.

Best Regards,
Brandon

TEST_NextOper.baq (40.2 KB)

Code:

select
[JobOper].[JobNum] as [JobOper_JobNum],
[JobOper].[AssemblySeq] as [JobOper_AssemblySeq],
[JobOper].[OprSeq] as [JobOper_OprSeq],
[JobOper].[OpCode] as [JobOper_OpCode],
[JobOpDtl].[ResourceGrpID] as [JobOpDtl_ResourceGrpID],
[ResourceGroup].[Description] as [ResourceGroup_Description],
(min(NextOper.NextOpr_OprSeq)) as [Calculated_MinNextOprSeq],
[NextOper].[NextJobOpDtl_ResourceGrpID] as [NextJobOpDtl_ResourceGrpID],
[NextOper].[NextRescGrp_Description] as [NextRescGrp_Description]
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
inner join Erp.ResourceGroup as ResourceGroup on
JobOpDtl.Company = ResourceGroup.Company
and JobOpDtl.ResourceGrpID = ResourceGroup.ResourceGrpID
left outer join (select
[NextOpr].[Company] as [NextOpr_Company],
[NextOpr].[JobNum] as [NextOpr_JobNum],
[NextOpr].[AssemblySeq] as [NextOpr_AssemblySeq],
[NextOpr].[OprSeq] as [NextOpr_OprSeq],
[NextJobOpDtl].[ResourceGrpID] as [NextJobOpDtl_ResourceGrpID],
[NextRescGrp].[Description] as [NextRescGrp_Description]
from Erp.JobOper as NextOpr
inner join Erp.JobOpDtl as NextJobOpDtl on
NextOpr.Company = NextJobOpDtl.Company
and NextOpr.JobNum = NextJobOpDtl.JobNum
and NextOpr.AssemblySeq = NextJobOpDtl.AssemblySeq
and NextOpr.OprSeq = NextJobOpDtl.OprSeq
inner join Erp.ResourceGroup as NextRescGrp on
NextJobOpDtl.Company = NextRescGrp.Company
and NextJobOpDtl.ResourceGrpID = NextRescGrp.ResourceGrpID) as NextOper on
NextOper.NextOpr_Company = JobOper.Company
and NextOper.NextOpr_JobNum = JobOper.JobNum
and NextOper.NextOpr_AssemblySeq = JobOper.AssemblySeq
and NextOper.NextOpr_OprSeq > JobOper.OprSeq
where (JobOper.JobNum = ‘TEST_NoJ2J_B5’)
group by [JobOper].[JobNum],
[JobOper].[AssemblySeq],
[JobOper].[OprSeq],
[JobOper].[OpCode],
[JobOpDtl].[ResourceGrpID],
[ResourceGroup].[Description],
[NextOper].[NextJobOpDtl_ResourceGrpID],
[NextOper].[NextRescGrp_Description]

I suspect it has something to do with JobOpDtl.OpDtlSeq.
Getting dupes is pretty common when trying to resolve ResourceGrps/Resrouces. from JobOpDtl to the OpSeq.
But…that is just off the top of my head,.I only took a quick look at your BAQ.

1 Like

Brandon

You do not require a SubQuery - you can use the LEAD & LAG Sql functions.

Example:
Create a calculated field:
LEAD(JobOper.OpCode,1)OVER(PARTITION BY JobOper.Company,JobOper.JobNum,JobOper.AssemblySeq ORDER BY JobOper.Company,JobOper.JobNum,JobOper.AssemblySeq, JobOper.OprSeq)

Hope this helps.

Roberto.

2 Likes

Thanks Roberto, I will try that!

Hi Roberto,

That definitely worked for my next operation. I had to do some reading on that function as I have never heard of it. Being relatively new to SQL, how would I apply this if I wanted to loop through the parent assembly when the operation does not have a next operation? (last operation in the set) Does this same function work there?

Thanks,
Brandon

1 Like

I was able to get this figured out. Thanks for all the help! If anyone needs this, I have uploaded my finished BAQ. Please let me know if you have any suggestions for improvement or questions.

TEST_NextOper.baq (86.2 KB)

1 Like