I have a BAQ that we use for our Material Move tag that uses a Subquery to bring in the next operation, desc, etc. The issue I am running into is this: 3 consecutive operations are Subcontract operations and I need to bring in the PO number that is tied to the Subcontract operation. I have the PO Rel table linked to the Job Oper table and joining on Company to Company and Job Num to Job Num. The date comes in, however, I am getting triple the number of lines.
Below is my code.
Thanks for any suggestions.
select [JobHead].[JobNum] as [JobHead_JobNum], [JobHead].[PartNum] as [JobHead_PartNum], [JobHead].[RevisionNum] as [JobHead_RevisionNum], [JobHead].[PartDescription] as [JobHead_PartDescription], [JobHead].[ProdQty] as [JobHead_ProdQty], [JobHead].[QtyCompleted] as [JobHead_QtyCompleted], [JobOper].[OprSeq] as [JobOper_OprSeq], [PORel].[PONum] as [PORel_PONum], [Vendor1].[Name] as [Vendor1_Name], [JobOper].[OpCode] as [JobOper_OpCode], [JobOper].[QtyCompleted] as [JobOper_QtyCompleted], [JobOper3].[OprSeq] as [JobOper3_OprSeq], [Vendor].[Name] as [Vendor_Name], [JobOper3].[OpCode] as [JobOper3_OpCode], [JobOper3].[QtyCompleted] as [JobOper3_QtyCompleted], [JobOper3].[CommentText] as [JobOper3_CommentText], [JobOper].[CommentText] as [JobOper_CommentText], [Part].[Number03] as [Part_Number03], [Part].[Number04] as [Part_Number04], [Part].[NetWeight] as [Part_NetWeight] from Erp.JobHead as JobHead inner join Erp.JobOper as JobOper on JobHead.Company = JobOper.Company and JobHead.JobNum = JobOper.JobNum left outer join (select [JobOper1].[Company] as [JobOper1_Company], [JobOper1].[JobNum] as [JobOper1_JobNum], [JobOper1].[AssemblySeq] as [JobOper1_AssemblySeq], [JobOper1].[OprSeq] as [JobOper1_OprSeq], (min( JobOper2.OprSeq )) as [Calculated_NextOper] from Erp.JobOper as JobOper1 inner join Erp.JobOper as JobOper2 on JobOper1.Company = JobOper2.Company and JobOper1.JobNum = JobOper2.JobNum and JobOper1.AssemblySeq = JobOper2.AssemblySeq and JobOper1.OprSeq < JobOper2.OprSeq group by [JobOper1].[Company], [JobOper1].[JobNum], [JobOper1].[AssemblySeq], [JobOper1].[OprSeq]) as SubLastJobOper on JobOper.Company = SubLastJobOper.JobOper1_Company and JobOper.JobNum = SubLastJobOper.JobOper1_JobNum and JobOper.AssemblySeq = SubLastJobOper.JobOper1_AssemblySeq and JobOper.OprSeq = SubLastJobOper.JobOper1_OprSeq left outer join Erp.JobOper as JobOper3 on SubLastJobOper.JobOper1_Company = JobOper3.Company and SubLastJobOper.JobOper1_JobNum = JobOper3.JobNum and SubLastJobOper.JobOper1_AssemblySeq = JobOper3.AssemblySeq and SubLastJobOper.Calculated_NextOper = JobOper3.OprSeq left outer join Erp.Vendor as Vendor on JobOper3.Company = Vendor.Company and JobOper3.VendorNum = Vendor.VendorNum left outer join Erp.Vendor as Vendor1 on JobOper.Company = Vendor1.Company and JobOper.VendorNum = Vendor1.VendorNum inner join Erp.PORel as PORel on JobOper.Company = PORel.Company and JobOper.JobNum = PORel.JobNum inner join Erp.Part as Part on JobHead.Company = Part.Company and JobHead.PartNum = Part.PartNum where (JobHead.JobClosed = false and JobHead.JobComplete = false and JobHead.JobFirm = true and JobHead.JobNum = @JobNumber)