Returning zero for null value in E10

Epicor ERP 10
1

I have subquery that totals misc charges for an invoice. If there are no misc charges on the invoice, then I get a blank field which does not allow me to do a formula for that invoice for grand totals.

Right now I just have a calculated field that says: MiscTotal = sum (InvcMisc.MiscCharge)

I need to enhance this to return a zero when there are no misc charges on the invoice. I’m in E10 (10.600)

Any help would be appreciated. I’ve tried the ISNULL, but I don’t think I set it up right or I need to go another way.

Thanks,
John

2

Try this.

case  
when  (sum (InvcMisc.MiscCharge)) is null then 0
else sum (InvcMisc.MiscCharge)
end
3

You can also use the ISNULL() function which automatically gives you an alternative if the value is null

ISNULL(sum (InvcMisc.MiscCharge),0)

The above will give you the Sum of InvcMisc.MiscCharge or 0 if that is null

2 Likes
4

Coalesce works here too. Not sure what will give the better performance, you may want to test both and see which query plan is better. I’m hosted or I’d check for ya :wink:

sum (coalesce(InvcMisc.MiscCharge,0))

That will set every null it encounters to 0 then do the sum.

(This is presuming you are doing this in SQL query, please ignore if incorrect)

1 Like
5

Thank you everyone. I tried the first two statements and they worked and I will try the Coalesce when I get a chance.

The actual field name is InvcMisc.MiscAmt, not InvcMisc.MiscCharge by the way.

Thanks again
John