And you only want to display operations where the job materials are not issued complete, correct?
Other way around: display Ops where the job materials ARE issued complete (that way they know what work to do next)
Okay. Do you know how to make a subquery in BAQ designer?
Search for “For XML PATH()” or String_Agg(), and you can aggregate multiple rows into one cell.
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.
It is easier than you think.
I think this link has a lot of good info about how to implement the subquery.
I still use the FOR XML PATH version, as String_Agg still doesn’t work in our version of epicor.
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.
1 Like
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.
Here is an example
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]
That’s what I originally posted.
1 Like
John, he wants that by operation. So he wants to show operations where materials have not been issued complete.
Thanks @utaylor , I missed that part. Then the assembly seq and opseq can be added in to the query and grouped on.
This is my original post, do you agree with the approach John?
1 Like
Yes, credit where credit is due. I truthfully skimmed the responses.
And why is there no embarrassed face?
I got it working. I was having trouble understanding most of the suggestions and plugged away on my own until I came up with this:
Subquery2 has one calculated field with just one result:
Then the main query has a calculated field which pulls out just the value from Subquery2:
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!
1 Like
Nice! Did you verify it is working like you want it to?
I am waiting on the person who requested this to verify that the results are correct. Fingers crossed!