I am having issues with display fields and retrieving more data than I need when executing a BAQ. I have followed the solution on the following thread where
the first query is marked as CTE with the joboper table. Following the steps the query returns the exact rows that I need with only the JobNum and calculated OprSeq field. I am wanting to add the operation description field on the top level as just the sequence numbers do not give much info on the current operation. When adding the JobOper table and OpDescription field to my top level query the BAQ is now simply returning all operations to the jobs. I have messed with joins and such but have currently came to a dead end. Any thoughts? @jkane Maybe you could help? https://www.epiusers.help/t/baq-current-open-operation-only/38616
What does your top level look like for tables and joins?
Why are you including the JobHead? If your CTE already has the JobNum and OprSeq, you should not need it unless there is data you need from it.
I need it to set criteria for a specific group of parts.
If you need data from it, it should be the third table. Join the CTE to JobOpr on Company, JobNum, and OprSeq and set the join to contain all records from the CTE (left outer join). Then join JobOpr to JobHead on Company and JobNum and set the join to contain all records from JobOpr (left outer join).
(@jkane beat me to posting cause I got pulled away, but I had it mostly typed it up so you get the same thing he said. lol)
I’m assuming that to get those current operations, there is a “group by” (the check boxes) for it, and an aggregate function, like Max(), or MIN() used. If you add more columns, you have to group by them, and then those rows become unique and are no longer grouped, hence all of the new rows showing up.
To get around that, you will need leave the extra info you need off in the subqueries/CTE then bring the table with the info you need into upper level, and rejoin to get the details.
So in your case, another copy of the JobOper table, then join on job number, assembly sequence, and operation sequence. Inner join. Then you can display the records that match the ones that were selected.
I attempted this assuming this is what you meant, but this was ignoring my criteria set on JobHead (assuming because of the join type to JobOpr). It was pulling records from all jobs it seemed.
However, I obtained what I needed by adding a matching join between JobHead and the CTE and changing the other joins to matching. Not sure I understand the logic behind that but it worked.
Your join is going to the wrong way. You have it set to “All Rows from Job Oper”. you want matching rows, or “All Rows from JobHead”
Here’s a visual example of how joins work, and a link to the article where I got it from.
Thanks a lot guys!