Help with BAQ

[mchernecki (Mike Chernecki)]

I am building a dashboard that will show jobs that are ready to ship but not necessarily complete (still charging materials). The last operation (MAX OprSeq) has been completed more than 3 days ago. I have started developing a query in SQL, but need to move it into BAQ. I am stuck on how to get the SELECT MAX query designed and working in the BAQ designer. Any ideas would be appreciated.
SQL Query

SELECT DISTINCT T1.jobnum,
T3.payrolldate,
T3.opcode
FROM erp.joboper T1
LEFT OUTER JOIN erp.labordtl T3
ON T1.company = T3.company
AND T1.jobnum = T3.jobnum
AND T1.assemblyseq = T3.assemblyseq
AND T1.oprseq = T3.oprseq
WHERE T1.jobcomplete = 0
AND T1.assemblyseq = 0
AND T1.opcomplete = 1
AND T3.complete = 1
AND T3.payrolldate < Dateadd(day, -3, Getdate())
AND T1.oprseq = (SELECT Max(T2.oprseq)
FROM erp.joboper T2
WHERE T2.jobnum = T1.jobnum
AND T2.assemblyseq = 0)

[ckrusen (Calvin Krusen)]

Use a sub query

[mchernecki (Mike Chernecki)]

That is what I am trying to do, but not sure how. My options are to drop in the phrase build of the top level query using a join or using subquery criteria 'selected values or field from specified subquery but I would need a way to pass the JobNum from the top level query.

[ckrusen (Calvin Krusen)]

1. Create a new subquery of type “Inner Sub Query”. It will default to being named Subquery2.
2. In the query designer (of Subquery2), add the JobOper table (it will name it JobOper1)
3. Set a table Criteria on JobOper1, of AssemblySeq = 0.
4. In the select columns of Subquery2 2, add Company, and JobNum
5. Create a calc field named MaxSeq with the expression MAX(JobOper1.OrpSeq)
6. Mark the group by boxes of the other two columns (Company and JobNum)
7. Select Subquery1, and add SubQuery2 to it.
8. Join SubQuery2 to JobOper (Company and JobNum)

That should get you started

[hkeric.wci (Haso Keric)]

I usually do it the same way you do it when it comes to getting a MAX Value for joining purposes or display purposes (like Last Approved Revision). I typically have much more Sub-Queries bundled together, it makes it much easier.

But if your Query is as simple as it looks, Sub-Query will prob be the best.

---

But just if you are curious, how a Sub-Select works in BAQ here is more info:

Using Count() in BAQ - Incorrect Syntax

@tmcmullen you can also do Sub-Select SubQuery. Then you wouldnt need to do GROUP BY on your Top-Query. In a Nutshell: Make a Sub-Query but DO NOT Use it on the Designer On your TopLevel Query add the Sub-Query to a Calculated Field On the Sub-Query under SubQuery Criteria you can add WHERE Clause. Example: SubQuery1: [image] [image] SubQuery2: [image] [image] Result: [image] [image] Basically in a simpler view it does SELECT (SELECT count(*) FROM table WHERe...) as YourCalculate…

The beauty is you don’t even have to display it to use it in a JOIN, let’s pretend you have SubQuery2 that is a TOP 1 and returns only 1 column. You can just enter it in the JOIN Criteria.

There is always a time and place for it, depending on complexity, and speed. Typically a Sub-Query (not a sub-select) will be faster, but if you are grouping stuff and have groups after groups after groups and wrapped into many queries, its tough to maintain.

[mchernecki (Mike Chernecki)]

Thanks for the links, that helpes find a solution. I did not know you could put a subquery name inside braces.