Jobs without method or without JobOper records

Epicor ERP 10
1

How to I capture records that are in JobHead but no JobOper records?

Basically if JobHead is table 1 and JobOper is table 2 - all records excluded from right outer join?

Is there a way to see all records in JobHead that don’t have a record (related by JobNum) in JobOper? records in table1 not found in table2?

2

You will not have any jobopr records without a job header but you can have
job header outer join with joboper if you want to see all job header
records regardless of whether they have any associated operations

Mark Wagner
Sr. Partner

Capstone Alliance Partners 888.597.2227 Ext. 71
<888.597.2227%20Ext.%20714>2 | 904.412.6847 mwagner@capstoneap.com (cell)
| www.capstoneap.com

3

If it were me, I would just make the BAQ with the job head and job oper tables with an outer join to include all of the the job head records. Make that an inner subqueary, and then with a new top level, grab the subqeury and you can filter that sub-query to find whenever the jobnum field for the job operation table is null. That should be a list of all jobs with no job oper records.

Does that make sense?

select 
	[JobSub].[JobHead_JobNum] as [JobHead_JobNum],
	[JobSub].[JobOper_JobNum] as [JobOper_JobNum]
from  (select 
	[JobHead].[JobNum] as [JobHead_JobNum],
	[JobOper].[JobNum] as [JobOper_JobNum]
from Erp.JobHead as JobHead
left outer join Erp.JobOper as JobOper on 
	JobHead.Company = JobOper.Company
And
	JobHead.JobNum = JobOper.JobNum)  as JobSub
 where (JobSub.JobOper_JobNum is null)
4

Thanks! I haven’t done a subquery yet but I’m guessing it isn’t too hard? I have the BAQ with those tables and the was playing with the different joins to try to get what I wanted but then realized I wanted what the join didn’t pull.

Is it easy to mark my query as a subquery and then add the top level? I’ll see what I can figure out tomorrow.

Thanks!

5

yup. If you need screen shots of what to do within the wizard I can get those for you. There is a sub query category about the tables that will show all of your sub queries. I only know SQL as well as I can google. I do all of my queries in the wizard. What I posted was what the system created for me.