Is thier anyway using the BAQ tool to do a left join/is null type statement?
I’m trying to find jobs that are missing all operations - so there would be a record in JOBHEAD with no match in JOBOPER
Isn’t that what you asked here?
Yes - Thank you! I couldn’t find it on my search. I still haven’t gotten it working. You would think there would be a simple way within the tool (such as left join/is null) as I can see many uses
Do you have the screenshots you mentioned in earlier post on how to get the subquery piece working?
Also - Is there a way to delete 2nd post so I don’t have two similar out there?
Just do a left join, then in the subquery criteria, add a criteria for the field that you are looking at to be null.
1 Like
I’m not looking for a field to be null - I’m looking for no matching record
IE - job head record but no joboper record for this jobnum
That’s the definition of null. If you have a job head record, but no job oper record. If they don’t match, and you do a left join, it will return the row with a null.
For that particular example:
Join to tables JobHed and JobOpr then in the Joing Type select all rows from JobHed
Then in the subquery criteria you can try to create a filter by selecting JobOpr table and then OprSeq field and in the condition ISNULL
1 Like
I’ll give it a try - I thought NULL in this case was an empty field - no records in JOBOPER have the field NULL - they just don’t exist it JOBOPER
similar statement of WHERE NOT EXISTS
I’ll let you know how it goes
SELECT Erp.JobHead.JobNum
FROM Erp.JobHead LEFT JOIN Erp.JobOper ON Erp.JobHead.JobNum = Erp.JobOper.JobNum
WHERE ((Erp.JobOper.JobNum) Is Null);
1 Like
That’s what I was trying to do - but since you can’t type in statement - you have to use the tool - I couldn’t figure out how to do it.
I did the sub-query (my first finally) and it worked - Thank you.
I now have
select
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobOper].[OprSeq] as [JobOper_OprSeq]
from Erp.JobHead as JobHead
left outer join Erp.JobOper as JobOper on
JobHead.Company = JobOper.Company
And
JobHead.JobNum = JobOper.JobNum
where (JobHead.JobClosed = FALSE)
and JobOper.JobNum is null
2 Likes
My preferred way in E10 is to do an OUTER join between jobhead and joboper, then summarize the job head, and have a calculated field called OperCount and make it equal to Count(JobOper.JobNum). THEN you get even more information… you can look for jobs with ZERO operations, ONE operation or even find the job with the most operations. But now, you are not looking for “null” but looking for a zero count.
5 Likes
I’m just looking to find jobs that are empty basically. On occasion, jobs are getting created by MRP and firmed that have no method. So nothing really gets scheduled (since there are no ops) but MRP thinks it is since a job exists for it.
More of a cleanup/catch all utility BAQ