Limits on expressions in aggregate functions in BAQs?

Epicor ERP 10
1

I have a BAQ with a subquery with the expression

sum(JobMtl.TotalCost/JobHead.ProdQty)

The displayed fields are:
image

So what exactly is going on to calculate the Calculated_MtlCost field?

I assume the Group by By on Company and JobNum limit the sum to records with common values for those fields.

Anyway … I’m running into when this BAQ is run when a Job is created, but no demand added (ie. no Prod Qty). Which gives me a div by zero error.

Can an expression like

sum(JobMtl.TotalCost/(case when JobHead.ProdQty = 0 then 1 else JobHead.ProdQty end))

be used?

In embedded programming (where most decimals were rounded anyway), I’d just us

sum(JobMtl.TotalCost / (JobHead.ProdQty+ 0.0000001) )
2

You should be able to do the first one with the case statement. I see no reason why that would not work.

1 Like
3

have you tried to put the if statement before the calculation

(case when JobHead.ProdQty = 0 then sum(JobMtl.TotalCost) else sum(JobMtl.TotalCost)/JobHead.ProdQty end)
2 Likes
4

I always do it that way to ensure not to divide by zero… works for me…

Pierre

1 Like