Looking for two OpCodes that appear on the same Job/Method

Epicor ERP 10
1

I have a BAQ where I am specifying that I’m looking for two OPCodes, for example: OPCodes 300 AND 3100 that would appear on the same job/method. When I say:

image
image972×445 23.6 KB

When I specify AND because I want both OP’s only if they appear on the same Job, I receive zero records. If I specify OR I receive ALL Jobs containing OP 300 or OP 3100 whether they appear on the same job or not.

Would you have a suggestion as to what I should be specifying or looking for so that the BAQ only pulls Job that have BOTH the OP 300 & 3100 on it?

Thanks!

2

I think you are close. Make up a subquery that pulls the list of operation sequence numbers for each job. Then in the top level use subquery criteria to do the same filter you have with the AND.

3

Hmmm I may have sent you down the wrong path. I couldn’t get this one to work either. I am sure there is a way to do it.

4

What you might try Judy, is make that an OR instead of an AND.

Then create a calculated field (integer) that is one if it’s one of the two operations else zero.

Next, sum the calculated field group by Part/Rev.

Finally, do a subquery condition. If it’s two, then both operations are on the PartRev.

1 Like
5

You start by making two subqueries. Each sub returns the job operations that match one of your two criteria. Then join the two subs at the top level on job number. Any jobs in both subs contain both ops.

select 
	[Has100].[JobOper_JobNum] as [JobOper_JobNum],
	[Has110].[JobOper1_JobNum] as [JobOper1_JobNum]
from  (select 
	[JobOper].[JobNum] as [JobOper_JobNum],
	[JobOper].[OprSeq] as [JobOper_OprSeq]
from Erp.JobOper as JobOper
where (JobOper.OprSeq = 100))  as Has100
inner join  (select 
	[JobOper1].[JobNum] as [JobOper1_JobNum],
	[JobOper1].[OprSeq] as [JobOper1_OprSeq]
from Erp.JobOper as JobOper1
where (JobOper1.OprSeq = 110))  as Has110 on 
	Has100.JobOper_JobNum = Has110.JobOper1_JobNum

EDIT: I like Marks approach better than this one.

6

Thank you for the effort!

7

@Nate, @mark
Trying this - I will let you both know! :slightly_smiling_face:

8

You could just drop joboper into the query twice. First one with opcode = 300, second one with opcode = 3100 and join them based on jobnum = jobnum

3 Likes
9

Ohhh! Slick! That is what we were all thinking but couldn’t get to!

1 Like
10

@Dtray - All right all that worked! Two subqueries one looking for the 1st OPCode the other subquery looking for the 2nd OPCode. Top Level Query have both Subqueries joined to it by JobNum.

Thank you all!!!

11

Don’t forget to mark @TDray’s excellent reply as the solution!

1 Like