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?
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).
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.
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.
@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.
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.