Tricky BAQ selection

Epicor ERP 10
1

I need to select GLJrnDtl records with the following odd criteria

It must select all the records for a certain date EXECPT those records where all the GLAccount for the ARInvoiceNum are the same.
Here’s an example of the data in the GLJrnDtl table

I want the BAQ to exclude rows for invoice 3456 because all the entries are for the same GL Acct.

I can hide these rows when the report renders by using CountDistinct(), but their values still add up into other aggregates (like SUM() ), which I don’t want.

Any hints on how to do this at the BAQ level?

2

Use a CTE and filter out the records you don’t want. Then use the results of the CTE to perform the second filter in a second query.

3

Dumb question de jour … What’s “CTE” stand for?

Does this require Sub-BAQs?

4

Common Table Expression.

Yes. You can change the query type on the SubQuery Options tab.

5

:grin:
https://goo.gl/0qCljd

1 Like
6

What’s this Google thing?
:wink:

7

Awe man I should have included the “internet explanation” here it goes lol
http://bfy.tw/90IG

8

Sucks that it adds the “LMGTFY” graphics. it’s SOOOO much better when someone clicks the link thinking it will take them right to the answer.

1 Like
9

Hey I fixed it! All you need to do is put a space before the link! Now the can be properly tricked!

1 Like
10

I’m gonna need some hand holding…

How do I even make a simple BAQ that only selects records where all the records of a group of records (my outer group here is ARInvoiceNum) have the same value for one field (the field being GLAccount)?

11

You might be able to use the Subquery Criteria tab on your count field. I’ve used this to filter against Calculated fields, might work for your case:

BAQSubQuery.jpg940×149 33.6 KB

12

If you can use straight SQL and maybe do it via external query, if your current version of Epicor doesn’t allow SQL in BAQ (i.e., we have 905702, i understand there is new capability in E10 with BAQs but don’t know details), would something like this work?

SELECT ARInvoiceNum, JournalNum, JournalLine, PostedDate, COUNT(GLAccount) AS Expr1
FROM GLJrnDtl
WHERE (PostedDate > ‘01/01/2014’)
GROUP BY ARInvoiceNum, JournalNum, JournalLine, PostedDate
Having Count(*) = 1

13

Any chance you guys have a recursive BOM query using a CTE?

14

Ther’es a recursive BOM query using CTE in the Epicor help

15

I looked for it in the docs on EpicWeb but couldn’t find it. I thought they went over it at an Insights as well.

16

Ther’es a recursive BOM query using CTE in the Epicor help

Just for fun, Google recursion.

17

Nice. I’ve got the BAQ from E10 help, I just don’t have an E10 system to build it on and need the SQL code. I should of made that clear.

18

If I fully understand the question then…

You can create an inner subquery that does a group by on ARInvoiceNum and GLAcct. Add a calculated field that provides a row count. COUNT(*)

Create a top level query that joins the table to the inner subquery on ARInvoiceNum and GLAcct. Apply criteria to only select inner subquery rows where rowcount = 1.