Hi all,
I was curious if anyone has written a BAQ they’re willing to share that has a "HAVING’ statement in the execution statement. A co-worker pointed out this statement in SQL to me yesterday, but when I attempted to implement in an Epicor BAQ it doesn’t behave quite as expected (I receive a grouping error).
Thanks!
There is a dropdown on the subwuery condition tab to select having
Yes, I did find the option for the Having statement, but it doesn’t behave as expected.
I’m attempting to replicate this SQL query in BAQ:
select count () as lot, lotnum, partnum from erp.partbin
where binnum like ‘mat%’
group by LotNum, partnum
having count()>1
I attached a copy of the BAQ at current state for reference.
ITTestHavingT.baq (11.2 KB)
What is it doing? (or not doing)
The goal of the query is to first count all the instances of part lots, and then filter down the results to only those that have more than one instance. Essentially trying to only display duplicate part lots (in different bins).
When adding the ‘having’ statement ,Count(PartBin_PartNum) > 1, the BAQ displays the error:
‘SubQuery1.PartBin_LotNum’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Even though the PartNum_LotNum field has been grouped in the BAQ.
You might be better off doing a count over partition to get the counts, then you can filter by the count later. If you do it this way, you can leave the detail in the rows.
Also, to get this to work, you need the PartBin.Lotnum, not PartNum.Lotnum. Which table (or subquery) it comes from is important too. You generally will have duplicate information/columns in the BAQ, but you can hide that in the dashboard.
Sorry, I miss-typed on that one.
My BAQ refers to PartBin.LotNum in all instances, (or PartBin_LotNum as applicable).
But the error says is missing that field from your subquery specifically. So even if you have the database table on that level, you have to include the field from the subquery.
Mostly I’m curious if someone has a working example of a having statement so I can review how the having statement functions.
Add the PartBin.LotNum from Subquery1 to your top level, and it will work. Edit I didn’t realize you were trying to add the having in the execution parameter.
edit
So I opened up the query and I see what you are trying to do now. The “having” is used with subquery criteria. I don’t think you can add it in the way you were trying to do it.
select
[PartBin].[LotNum] as [PartBin_LotNum],
[PartBin].[PartNum] as [PartBin_PartNum],
(count(PartBin.PartNum)) as [Calculated_couting]
from Erp.PartBin as PartBin
group by [PartBin].[LotNum],
[PartBin].[PartNum]
having (count(PartBin.PartNum)) > 1
You need a calculated field to get your count
Then you add the subquery criteria, there’s a check box to add the having there.
ITTestHavingT_fixed.baq (11.8 KB)
Thank you!
I had completed forgotten there was a subQuery Criteria tab!
