BAQ with Subquery

Epicor ERP 10
1

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

2

If you have 3 matches, you will get 3 lines.

If you want them on one line, there are ways to do that. To concatenate whatever shows up, you can use this method. It’s kind of a pain to set up, but works nicely.

If you will always have the same records, you can make 2 subqueries, and limit them so you only get one per subquery back, then you can have 3 columns.

What is it that you want it to look like in the end?

3

I would like one row with the following: Job#, Part#, Part Desc, Part Rev, Prod Qty, Job Comp Qty, Current OpSeq, Current Op Code, Current Op Desc, Current OP Job Comments, Current OP PO#, Current OP Vendor name , Current Op Qty completed… (then the same for the Next Operation). Next OPSeq, Next Op Seq description, Next Op code, Next OP Job comments.
The dataset is then used in our material move tag where the top half of the move tag reflects everything from the current operation and the bottom half of the tag reflects the next operation so the material move handler knows where to move the product to or if it is going to another subcontract operation the shipping personnel know not to send it back out to the floor but to another vendor.
Hope this makes sense…
thanks
carol

4

I think I will try the subquery option and limit the results and see if that will work. Concantenate I don’t believe is the answer to my issue.
Thank you for the suggestions.
Carol :slight_smile:

5

If you’re getting dups, its because your missing a relationship. Probably the AssySeq

Joining JobOper to JobHead on Job Num, should give you a row for each Operation, for Assy, on the job. If a job has 2 Operations and 3 Assy’s youd get 6 rows.

1 Like
6

I think the link you’re missing is

JobOper.OpSeq = JobMtl.RelatedOperation

And the link between JobOper and JobMtl should be an outer left join. Else you’d miss Materials not assigned to an operation

1 Like
7

Thank you Calvin!!

8

Hi Carol,

Would you be willing to share the BAQ itself? I have a user that is requesting something similar.

Thanks,
Brandon

9

I actually ended up using a SQL view. I can certainly send that to you. Would you provide your email address?
Carol

10

I’ve attached the code in a .txt document

(Attachment Material Tag View is missing)

11

brandon.holloway@dorner.com Thank you.