If you are querying job material maybe you should make a subquery on that table and group by the company,jobnum,asm seq, and related operation. Then make two calculated fields. One will be a sum of the field IssuedComplete and the other will be a count of mtlseq.
Now if you join that subquery to your top query on joboper.company = subquery.company and joboper.jobnum = subquery.jobnum, and joboper.asmseq = subquery.asmseq and joboper.oprseq = subquery.oprseq… you will have one line.
at this point if the subquery.Calculated_SumIssued complete = Calculated_Count(MtlSeq) then you know that all of the materials have been issued complete since the sum of issued complete is the same as the count of materials.
I do not, I have done many BAQs but never a subquery. I have clicked through the subquery tabs but they don’t make any sense to me and looking at the Epicor Help on subqueries has left me even more confused. This topic is a little over my head.
Nate, thank you for that. I was getting hung up on a low-level GUI issue: how to switch to the subquery. Now that I realize I can use the left and right arrows to go between the two queries, suddenly subqueries don’t seem so complicated like you suggest.
I am thankful for people getting me this far, but so far everyone has been pointing out how to concatenate rows into a single field. I can do that, but it doesn’t solve my problem. If I have a job with four materials and three are issued complete but the fourth is not, doing a concat on the material is simply going to return a concat’d value with three of the four materials and it will show up as a result. The need here is to only show jobs where material is issued complete for all materials on the job, and this concat and subquery stuff isn’t getting me there.
You still have to filter the subquery to include the rows that you want. This would be the same thing you would do on your top level query that you were doing before, just in the subquery.
Yes I understand that but I want the rows returned if and only if ALL materials on that job are issued complete. I can filter for issued complete, but that just returns a concat’d value with three of the four materials, so does not actually filter in the way that I need it to filter. How do I return a row only if ALL materials are issued complete? That is what I am struggling with.
Do a subquery with a calculated field where a material is issued complete, then return 0 and if not, return 1. Then sum up those numbers and only include rows where the sum = 0. You wouldn’t actually need the sum_agg() function for that, just a subquery to get your list that acts as your filter.
Even easier (in my opinion) would be to create the material query as a CTE and group on job and issued complete and count where issued complete = true and count the material sequences. Then bring the CTE into the next subquery and filter where the count of issued complete = the count of material sequence.
select
[JobMtl].[JobNum] as [JobMtl_JobNum],
(sum(
case
when JobMtl.IssuedComplete = 1 then 1
else 0
end
)) as [Calculated_IssuedComplete],
(count(JobMtl.MtlSeq)) as [Calculated_MtlSeq]
from Erp.JobMtl as JobMtl
where (JobMtl.Company = 'gtr' and JobMtl.JobComplete = false)
group by [JobMtl].[JobNum]
Then the dashboard which uses this BAQ filters based on that calculated field. Maybe not the most elegant thing but it is the only thing I could figure out.
My first ever subquery! Thank you everyone for the pointers!
