BAQ equivalent for this SQL Query?

Epicor ERP 10
1

I am attempting to add some business logic to a BAQ that drives a dashboard. Below is a SQL query that I wrote as a proof of concept, but I’m new to Epicor and BAQs, so I’m not sure how to accomplish this.

In summary, the user wants to know whether non-backflushed components for a given job have all been issued complete. Assuming that I can get these counts, I intend to use a calculated field to return a 1 if the IssuedComplete count matches the other two counts and otherwise return a 0.

What I have tried so far is to add a subquery to the existing BAQ, but I keep getting a ‘Caclulated Field not Found’ error when I Analyze or get a list of the items. Long story short, I haven’t gotten very far. This is being added to an existing BAQ with lots of joins and calculated fields - so I’m hesitant to start from scratch before having strong confidence that this approach is feasible.

Thanks in advance for any insights the community can add.

SELECT
a.JobNum,
(SELECT COUNT() FROM Erp.JobMtl AS c WHERE c.JobNum = a.JobNum) AS MatlCount,
(SELECT COUNT() FROM Erp.JobMtl AS d WHERE d.JobNum = a.JobNum AND d.BackFlush = 0) AS BackFlushCount,
(SELECT COUNT(*) FROM Erp.JobMtl AS e WHERE e.JobNum = a.JobNum AND e.BackFlush = 0 AND e.IssuedComplete = 1) AS IssuedCompleteCount
FROM Erp.JobHead AS a
LEFT JOIN Erp.JobMtl AS b ON a.JobNum = b.JobNum
WHERE a.Company = ‘mycompany’
AND a.JobFirm = 1
AND a.JobReleased = 1
AND a.JobComplete = 0
AND a.ProdQty > a.QtyCompleted
GROUP BY
a.JobNum,
b.BackFlush,
b.IssuedComplete
ORDER BY a.JobNum

2

You need to do a sub query that returns just the count in that sub query add a subwuery filter that is conditional on the top level query

Then in the calculated field in the top level query drag the entire subwuery in {subwuery1}

1 Like