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]