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)
Carol