I am trying to calculate the Tie of a pallet, but the search returns an error if the width field is

Question

Hello,

I am trying to calculate the Tie of a pallet, but the search returns an error if the width and/or depth field is blank.

The following formula works, but if these fields are not populate the search will return an error.   I am concerned that a NULLIF will not work, as that would mean I was dividing by "0".

FLOOR (1920/({custitem_width}*{custitem_depth}))

Any assistance would be appreciated ,as SQL is not my thing.

Kristin

Herdi Rana-Oracle · Accepted Answer

Hi @KristinAvril,

Nullif function returns a null value if the two arguments are equal, otherwise, it returns the first argument.

The common error for this would be the zero divisor error, in which it would be thrown if one of the field is null: let us say that your Custom Width is 5 but the depth is null, then it will be computed as Zero (hence the error) or if both item. However, if both are null, this will not error out and the returned result would be null.

This is where the Nullif expression takes into place. For records that computes the width * depth as zero, it will return as null, hence bypassing the error in the saved search.

I tested this one on my end using the Nullif function (wherein my numeric field is also null). Note that I used standard fields for my testing. I believe the {custitem_width} and {custitem_depth} is a custom field am I correct?

I tried this one on two of my items: One item wherein both my numeric/standard field is null:

And the other one with the item weight populated:

For the second item, this will be computed as zero, hence it will error out.

I then incorporated the Nullif expression:

FLOOR (1920/ Nullif(({weight}*{shippingrate}),0))

Whereas the formula would return Null value if (weight*shipping rate) is equal to 0. But if (weight *shipping rate) is not equal to zero, then it will return the first expression which is the (weight *Shipping rate). Kindly try this formula in your end and see if this still throws an error.

Hope this helps!

@Herdi Rana-Oracle