How do I write a BAQ subquery to get the last date of a job from a group of parts.
I know the SQL but I don’t understand how to do that in the BAQ editor. Is something like this even possible in the editor?
SQL:
SELECT p.PartNum, p.Description,
(SELECT TOP(1) j.JobNum
FROM [erp].JobHead AS j
WHERE j.PartNum = p.PartNum
AND j.Company = p.Company
ORDER BY j.JobNum DESC
) AS 'LastJob'
FROM [erp].Part AS p
WHERE p.ClassID IN ('a', 'b', 'c')Are you using E10(tag) or 9.05(profile)? I know it’s possible in E10. It would be a top level part query and a subquery on part/jobhead. I don’t believe you have the function of top(1) but you would probably want to run a MAX(JobNum).
This is in E10. Okay so it seems like it’s possible.
Is there any documentation that anyone knows of or a video tutorial of how to do this in E10 BAQ editor?
How do you even make a subquery? The interface doesn’t really show how.
There are quite a few examples on this site if you search for it. Being familiar with SQL definitely helps with imagining the structures.
This is where all the magic starts.
EpicWeb has a pretty good step by step for learning BAQ’s, and lots of other tools. A good place to start anyways.
So I made the sub query and set the display field and I think I linked it correctly. How do I show the sub-query results along with the top level query results?
When I go to Analyze tab, my column from the subquery does not appear.
when you are on the top level, there is a button to show the subqueries. you then bring in the subquery like any other table, link it, then you can choose which of the subquery fields you want to display on your top level.
Does anyone know how to do a group by with BAQ editor?
It looks like the subquery can link to the main query however in order to do that I need to select fields in the subquery’s display fields.
However I want to use an aggregate in the subquery to get last job. And of course if you use an aggregate, SQL requires a GROUP BY clause.
I wish there was a way to write raw SQL right in the BAQ editor? that would save so much time.
Did you look at the Epic Web link I posted?
No I couldn’t it’s behind a password.
Talk to your CAM and get access to that. If your on service, you should have access. It will answer most of your questions.
Even the regular help has a lot of the basics. Not a step by step, but it explains the basics like you are looking for.
You sure can Scott. This should get you there.
You can use a Window Function and get the last date without having to do the sub-query. Your formula would look something like:
MAX(JobNum) over (partition by JobHead.PartNum order by JobHead.CreateDate)Here is an example, grouping by ‘Max oper sequence’ :
This shows a subquery which is then brought in to the upper level Top query.
With everyone’s assistance here I was able to figure out how to do it.
I then created a quick video of how creating a sub-query is accomplished. Hopefully this video will help someone else just starting out with sub-queries.
BAQ Subquery video: BAQ-Subquery-Creation.mp4 (6.7 MB)
@smason - nice video, but i do not think that is correct. You are returning multiple jobs per part still. If you removed the jobnum from the subquery you would then get the last job per part. The other issue with using MAX(JobNum) would be that they are not all INTs. You would more likely have to go by job date/time. You would also want to create a left join to see the parts that never had a job as well.
Job numbers are characters so, wouldn’t MAX get the highest firm job number? (And for our use case they don’t want to look at unfirmed aka MRP generated jobs).
Also does subquery Top Clause do anyting? I assumed it would be like a TOP in SQL.
If you use the top 1 that you have shown, it will only return one row for the whole subquery, so you will only get one match.
If you want to get the matches for one field per row (like the last date a part was used) you can use this technique where the subquery and the matches act on each row individually, so that top 1 becomes very useful then.
If the subquery is made correctly, you would only receive 1 row per partnum and the top(1) wouldn’t even need to be there. Since there is a group by jobnum you are returning all jobnums to your sub query and creating a row per jobnum which isn’t a max anymore. If you ignore the MRP jobs and MAX(JobNum) without the group by jobnum (remove the field completely from the sub query) it should be what you were looking for.
Also, don’t forget that you can see the SQL that the BAQ is generating on the General tab. So if you know that you want in SQL, you can try things and see what the system is generating and see if it matches what you think it should be doing.
