BAQ formula incorporating "If" and "sum"

Epicor ERP 10
1

I would appreciate any assistance with this E10 BAQ formula. First, let me begin by saying what the BAQ is about. I’m trying to summarize each Purchase Order Line’s quantity received. The reason I want to summarize is to avoid the baq results from displaying multiple PO Line receipts. In addition, if there are zero receipts against a PO Line, then I want the BAQ results to show 0 versus blank.

So here is the formula I was trying to get to work.

(case when sum(RcvDtl.VendorQty = ’ ') then 0 else sum(RcvDtl.VendorQty) end)

The error comes up with… Incorrect Syntax near ‘)’.

I did setup the BAQ with the following fields within the Advanced Group By Clause SQL Editor
RcvDtl.PONum
RcvDtl.POLine
RcvDtl.PartNum

Thank you for any feedback

2

Hi @tdejager

The bracket ) needs to be before the =

Try this

case    when sum(RcvDtl.VendorQty) = ' ' then 0 else sum(RcvDtl.VendorQty)
end
2 Likes
3

I would do a CTE query first that outputs a value for each line and then use the CTE as the base for the second query and sum on that.

Do a query with no grouping and put in a calculated field for the Qty

Case When RcvDtl.VendorQty = '' Then 0 else RcvDtl.VendorQty End

Then do the grouping on the second query and just sum your calculated field.

2 Likes
4

@tdejager Are you familiar with subqueries? I was thumbing through your older posts and I don’t see where you ever use them, and you come from an E9 background where they didn’t exist.

Sums and things in E10+ are best done with subqueries.

To get your zero, you’ll use isnull(SomeField, 0) where SomeField is the calculated sum field from your subquery.

3 Likes
5

Jason, this is actually a subquery and I do have experiences with subs. To provide a bigger picture, my original post was only explaining the issue I was having within my InnerSubquery.
But you’ve provided an excellent solution, so here’s what I proceeded to do. I went back to my InnerSubquery and change the formula back to the sum(RcvDtl.VendorQty). Next I went back to my TopLevel query and created the exact formula you’ve mentioned. And BINGO!

Thank you very much to all who’ve taken the time to respond, I greatly appreciate it!!! I tried everyone’s response, and while they got me past my error message, it was Jason’s response that turned out to be the one that got me to what the end goal needs to show. Again, thank you to all!!

1 Like