Help going from SQL to BAQ: Duplicate Job Material Part Numbers

Epicor ERP 10
,
1

I am not sure how to do this in a BAQ. Any help would be appreciated. The below returns me the duplicate part numbers within the same Assembly.

How do I create the Count(*) and the Having statement?

SELECT
[JobNum],[AssemblySeq], [PartNum], [QtyPer], COUNT()
FROM
Erp.JobMtl
Where [Company] = 300 AND [JobNum] = ‘085649’
GROUP BY
[JobNum], [AssemblySeq], [PartNum], [QtyPer]
HAVING
COUNT() > 1

2

One way you could do it is:
SubQuery to get count linked to main query, then subquery criteria to ensure count > 1

3

The Having clause is located on the SubQuery Criteria tab:

image
image.png876×664 34.7 KB

1 Like
4

Thanks! How do I get the calculated field equivalent of Count (*)? I must be missing something obvious.

image
image.png931×120 4.82 KB

5

@Mark_Wonsil it’s almost like we’re completing each others __________

1 Like
6

Nevermind… I was getting a syntax error false positive because Field Name was ‘Count’.

7

That one bites me quite often as well

1 Like