I am trying to make a BAQ that only shows “Open Jobs” that have both the SubQuery Criteria set to resource groups “Waterjet” and “Sew 04”. I cannot get the BAQ to work using both of these. How do I get this BAQ to do this?
The criteria should be RG IN [waterjet,sew04]
1 Like
I did this with a 2-level query. The subquery takes the job and assembly number as groups, and adds a calculated field to merge all the resopurce groups together.
String_Agg(JobOpDtl.ResourceGrpID, ', ')
Then in the top-level query, I set the subquery criteria to the calculated field, using a LIKE operator, and inserting the resource groups in a constant filter like this:
“%your RG ID%”
This seemed to work for me! In my example SQL below 4A and MCU are the resource group IDs I used.
Good luck!
select
[SubQuery1].[JobHead_JobNum] as [JobHead_JobNum],
[SubQuery1].[JobOpDtl_AssemblySeq] as [JobOpDtl_AssemblySeq]
from (select
[JobHead].[JobNum] as [JobHead_JobNum],
[JobOpDtl].[AssemblySeq] as [JobOpDtl_AssemblySeq],
(String_Agg(JobOpDtl.ResourceGrpID, ', ')) as [Calculated_rgidS]
from Erp.JobHead as JobHead
inner join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
inner join Erp.JobOper as JobOper on
JobAsmbl.Company = JobOper.Company
and JobAsmbl.JobNum = JobOper.JobNum
and JobAsmbl.AssemblySeq = JobOper.AssemblySeq
inner join Erp.JobOpDtl as JobOpDtl on
JobOper.Company = JobOpDtl.Company
and JobOper.JobNum = JobOpDtl.JobNum
and JobOper.AssemblySeq = JobOpDtl.AssemblySeq
and JobOper.OprSeq = JobOpDtl.OprSeq
where (JobHead.JobClosed = false)
group by [JobHead].[JobNum],
[JobOpDtl].[AssemblySeq]) as SubQuery1
where (SubQuery1.Calculated_rgidS like '"%4A%"' and SubQuery1.Calculated_rgidS like '"%MCU%"')
1 Like
Thats way easier. You should probably so it that way instead! 
1 Like
I have the ResourceGrpID set as IN with [waterjet,sew 04] as the value in the constant list. It is still not returning any information. Here is a screen shot of the phrase builder.
In the constant list, make one entry on each row. I don’t think the [] brackets need to be there.
Also to make sure you get all the details, make sure you put JobAsmbl between JobHead and JobOper.
1 Like
I set everything as you have shown and it is now returning information but it is showing all jobs with either waterjet or sew04 and not just the jobs that have both operations on the MOM. What else do I need to do?
Thats tricky. I think you could try the method I proposed, though a bit more complicated.
testes.baq (32.1 KB)
It appears that the BAQ is a later version than we are on. It errored as being created in 4.2.200.0 and we are on Kinetic and I am not sure of the version.
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
select
[SubQuery1].[JobAsmbl_JobNum] as [JobAsmbl_JobNum],
[SubQuery1].[JobAsmbl_AssemblySeq] as [JobAsmbl_AssemblySeq],
[SubQuery1].[JobAsmbl_PartNum] as [JobAsmbl_PartNum]
from (select
[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
(String_Agg(JobOpDtl.ResourceGrpID, ', ')) as [Calculated_RGIDs]
from Erp.JobHead as JobHead
inner join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
inner join Erp.JobOper as JobOper on
JobAsmbl.Company = JobOper.Company
and JobAsmbl.JobNum = JobOper.JobNum
and JobAsmbl.AssemblySeq = JobOper.AssemblySeq
inner join Erp.JobOpDtl as JobOpDtl on
JobOper.Company = JobOpDtl.Company
and JobOper.JobNum = JobOpDtl.JobNum
and JobOper.AssemblySeq = JobOpDtl.AssemblySeq
and JobOper.OprSeq = JobOpDtl.OprSeq
where (JobHead.JobClosed = false)
group by [JobAsmbl].[JobNum],
[JobAsmbl].[AssemblySeq],
[JobAsmbl].[PartNum]) as SubQuery1
where (SubQuery1.Calculated_RGIDs like '%4A%' and SubQuery1.Calculated_RGIDs like '%MCU%')
Sorry, dang versions! Here is the SQL in case you can rebuild it from that.
Wait, you only want jobs that have both?
You should do 2 criteria lines then.
(RG = waterjet
and rg = sew04)
You are in the SubQuery Criteria tab. You need to do that in the Table Criteria tab. You also need to enter the parentheses.
That did not do it either. Not sure what I am doing wrong but I followed your instructions above and it still does not return information.
@Jarhead I would do this with filtering sub queries. One for each RG.
Top query just jobhead
join on company and jobnum
job1 and job2 the same
