Why are you joining to rcvdtl and then to your subquery? Get rid of that and join from part to your subquery.
I need another field from RcvDtl. I originally had the subquery joined to part and then RcvDtl joined to the subquery but switched those around to try and join on company. The subquery does not have Company in it.
If you need rcvdtl separately then directly join from part to the subquery. Chaining those together you are running the sum individually for each and every rcvdtl record that is returned. And always make sure you include company in the subquery and ALWAYS join on company. It doesn’t matter if you only have one company.
It doesn’t appear to have made it any faster. Thank you for taking the time to try and help me speed it up!
What does it look like now?
What version are you on @jott ? If you are on a new version, then you can try adding option(recompile) and see if that kicks it into gear.
1 Like
I’ll politely semi-disagree with that. Always try joining on company. However, sometimes (even usually) that also tanks query performance for inscrutable reasons. So also try joining without. Go with whichever is faster.
I think I’m still pretty much in the same place query wise.
@Banderson, I tried the recompile and after 5 or so minutes I’d get an error and 0 results. I removed it from the execution settings and am back to getting 800 results in 4 minutes. I’m on 2023.1.10 by the way.
What was the error?
It said something like Application Error: Application Server cannot be reached. Something to that effect. I ended up just splitting the query back up into my original query and made a second query containing the fields that were newly requested. Then made it so that the grid containing the new fields was “subscribed” to the original grid. It seems to work, still waiting for hear any feedback the users have.
That’s what “Specified table field value” is for. Just make sure the order of your tables puts the needed table first. Like this - this is a criteria on PartCost that is referencing Part:
So, I agree, but, if you skip the company-join, then add one of these to the table:
I’ve experienced this (a filter on Company) speeding things up for no good reason at all. That’s a @timshuwy trick, I’m pretty sure.
Pretty neat, one of those options I have seen, just not when I needed it 
Do you happen to know of any other ways to use aggregate functions outside of a subquery? For some reason I thought there was some other way, I just haven’t been able to find it.
What do you mean “outside of a subquery”? Do you mean without grouping?
You can use windowing functions to use aggregates without grouping. Here’s a video with a bunch of tips.
1 Like
That is a great video. @Banderson really explains those things very thoroughly, even for an anti-coder like me.
Yes, I meant without having to create a subquery, in the top level. Thanks for the link, I’ll take a look!
You can group (that’s what is generally required to use aggregate functions) on the top level just fine. You don’t need to create a subquery to do that. I’m pretty sure there is some terminology being mixed up here.
Well you can but you’d need to group by every other field in the query no? I have tried grouping by say part number in order to create a calculated field using an aggregate function and seemed to always get errors.
I do know they have the advanced group by editor, but I always had trouble using that as well. I’ll watch your video and see what I might be missing.
That’s correct. If you are trying to add all of the stuff on 3 rows into a single value, where do you put that value? Traditional aggregates turn the 3 rows into 1 row, and you can’t do that without grouping everything.
Windowing functions will do the aggregate and put it in all 3 rows. The video will show you how to do that.
I’ve never dug into the advanced group by myself, so I’m not sure how that works. (I should probably do that one of these days)
1 Like
Have a look at the attached to see if this version runs quicker - slightly different approach
EPA_PartOnHandReceived.baq (46.2 KB)
Sue! This seemed like a good start. I finished building the query out with what I needed and am getting a ton of duplicate rows. I tried to make the top level query distinct and am getting errors. Seems like it’s brought me to a similar place as my original query. Do you know how the CTE’s operate differently from Inner Subqueries in BAQs? I can’t really tell if it does anything different on the front end, seems like it may just change what’s going on on the backend. I really appreciate you spending time on it!