Calculated Column Error in BAQ

Epicor ERP 10
1

I have a BAQ with Subqueries. It basically totals up each invoice for Sales with no freight charges for a selected time period. I am trying to compare that with my customer list and for each customer that didn’t have an invoice for that time period, I want it to add a 0.00 to the Invoice Total Field so I can use it when I’m comparing sales from year to year. I have the Total Field that is a Decimal file. I have created a Calculated Field below:

image
image.png972×606 27.4 KB

When I try to run the Query, I get this error. It does Analyze as OK though.

image

2

then ‘0’

should be:
then 0

3

I tried that am I’m still getting the same error.

image
image.png911×560 24.4 KB

image

4

What is the type of InvoiceTotals1.Calculate_TotalNoFrght? Hopefully decimal

If its decimal, you cant do the first comparison = ‘’
If its a nvarchar, you cant assign it to your value (without a cast)

5

try ISNULL, as you can not compare decimal variable to ’ ’ blank

image
image.png925×617 28.7 KB

6

Thank you this worked!

image
image.png927×596 26.5 KB

7

If you want shorter syntax, and you just need to check for a null value to replace, I like to use the IsNull():
IsNull(invoiceTotals1.Calculated_TotalNoFrght, 0)
I find it’s quicker to understand when I’m troubleshooting.

8

I didn’t know that you could pass value for it to use when the first param is indeed null.

So IsNull(invoiceTotals1.Calculated_TotalNoFrght) returns a boolean, but

IsNull(invoiceTotals1.Calculated_TotalNoFrght, 0) returns an int?

Does the deafault value provided have to be the same type as the expression tested?

Is IsNull(invoiceTotals1.Calculated_TotalNoFrght, "N/A") valid? (I’d think not)

9

Per MS:
Returns the same type as check_expression . If a literal NULL is provided as check_expression , returns the datatype of the replacement_value . If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int .

10

Okay, one more for you. I am trying to calculate a % on the Sales Columns. For some customers both columns might be null. I have this formula so far but am still getting an error.

image
image.png923×595 27.1 KB

image

11

Need to test for zero too.

EDIT

(IsNull(YTDSales4.Calculated_YTDSales4,0)-IsNull(YTDSales3.Calculated_YTDSales3,0)) / MAX(IsNull(YTDSales3.Calculated_YTDSales3,1),1)

Also should test each of YTDSales3 and 4 as null before doing math.

Note: Should test for exactly zero. I cheated to just assume that the YTDSales3 will be either 0 or some much larger number)

Edit 2

Not sure if that MAX(a,b) function works in SQL, as it might be the aggregate function

Edit 3

Now I’m sure that the MAX() function wont work. Use some If Then’s or Case When’s

12

Okay, makes sense…do you have an example of how to do that in combination with test for Null

13

Just nest your case statements. You already no how to test for 0.

14

@ckrusen
Okay, I am getting a formula headache :tired_face: Sorry, I am still learning how to code in Epicor, let alone C#.

I don’t know how to nest the 0 and null statements together.
This is what I’ve got with an error still.
(IsNull(YTDSales4.Calculated_YTDSales4,0)-IsNull(YTDSales3.Calculated_YTDSales3,0)) / MAX(IsNull(YTDSales3.Calculated_YTDSales3,1))

15

The MAX function won’t work.

Go back to using case when
and this is really SQL language not c# (I think)

1 Like
16

Okay, whatever language it is I am still a newbie lol. I will try that.

17

think of your case statements (or anything else) like pieces. check for nulls first. isnull(blahblahblah) or (case when x=o then end) or whatever you need. Then take all of the piece out, and copy in to notebook or something and replace it all with X or something. Then do your next case statement around it. Then when you have all of the logic figured out replace your X’s with the pieces that you copied out.

It’s just like algebra.
2x+y=c
x = (102)
y = (30-5300)

replace x and with with the stuff and you end up with

2(102) + (30-5300) = C

You can make as many case statements within case statements as you want, they just get confusing.

18

BTW SQL will let you divide by NULL but not by zero.
If you divide by NULL you will get NULL.
If you do not want NULL as a result then you can check for NULL AFTER the division:
So you can use ISNULL and NULLIF to toggle your NULLs to zeros or any value to NULL.

An example to consider:
ISNULL((76 / NULLIF( 0, 0 )),0)
RESULT = 0

19 
case  
    when (OrderDtl.DiscountPercent) is null  then (IsNull(OrderDtl.UnitPrice,0)- IsNull(OrderDtl.DiscountPercent,0))/1

    when OrderDtl.DiscountPercent = 0 then (IsNull(OrderDtl.UnitPrice,0)- OrderDtl.DiscountPercent)/1 

    else (IsNull(OrderDtl.UnitPrice,0)- OrderDtl.DiscountPercent)/ OrderDtl.DiscountPercent

end

DiscountPercent is YTD3, and UnitPrice is YTD4

The first when checks for YTD3 being null.
The second checks for YTD being 0
The third (else) is when it is neither.

I don’t check for YTD3 being null in the 2nd when, or else.

EDIT:

Could have made the first two cases just one:

when IsNull(OrderDtl.DiscountPercent,0) =0  then (IsNull(OrderDtl.UnitPrice,0)- IsNull(OrderDtl.DiscountPercent,0))/1
20

Rick - does SQL make the result of any math operation containing at least one operand being null, into null?

assuming: a=10, b=null, c = 0

a + b + c = null
a - b = null
a * b = null
a / b = null
b / a = null

What does b / c yield?