SQL Help on Decimal/Integer Math

Epicor ERP 10
1

I have a calculated decimal field that here is my end goal. I don’t think I need the CAST, but its there.

case when Part.UD_UsePFI_c = 1 then 
round((ShipDtl.OurInventoryShipQty + ShipDtl.OurJobShipQty) / CAST(OrderDtl.PFI_FEET_c AS decimal) + (OrderDtl.PFI_INCHES_c / 12.0) , 0)
else ShipDtl.OurInventoryShipQty + ShipDtl.OurJobShipQty end

This is part of a Union, if that matters.

I validate the statement and the BAQ says its OK. But when I test it, I get “Bad SQL Statement”.
I have narrowed it down.
This works:

case when Part.UD_UsePFI_c = 1 then 
OrderDtl.PFI_FEET_c + OrderDtl.PFI_INCHES_c / 12
else ShipDtl.OurInventoryShipQty + ShipDtl.OurJobShipQty end

But this does not:

case when Part.UD_UsePFI_c = 1 then 
500/(OrderDtl.PFI_FEET_c + OrderDtl.PFI_INCHES_c / 12)
else ShipDtl.OurInventoryShipQty + ShipDtl.OurJobShipQty end
2

I’d say you were missing a parenthesis to establish the order of operations.

says PFI_INCHES/12, Plus PFI_FEET, divided into 500. is that what you mean? I’d probably add two parenthesis to do this:

and see what happens… I’m not where I can test this for you, sorry…

3

Since this expands into the SQL phrase, I’d put the whole thing in a set a parenthesis:

(case when Part.UD_UsePFI_c = 1 then 
round((ShipDtl.OurInventoryShipQty + ShipDtl.OurJobShipQty) / CAST(OrderDtl.PFI_FEET_c AS decimal) + (OrderDtl.PFI_INCHES_c / 12.0) , 0)
else ShipDtl.OurInventoryShipQty + ShipDtl.OurJobShipQty end)

BAQ builder does that when you double click the If x Then y Else z

image
image701×225 8.74 KB

4

@MikeGross, thanks for catching my order of operations. I have fixed it as below.
@ckrusen, I added the extra parentheses, but still getting “Bad SQL statement”.

(case when Part.UD_UsePFI_c = 1 then 
round((ShipDtl.OurInventoryShipQty + ShipDtl.OurJobShipQty) / (CAST(OrderDtl.PFI_FEET_c AS decimal) + (OrderDtl.PFI_INCHES_c / 12.0)) , 0)
else ShipDtl.OurInventoryShipQty + ShipDtl.OurJobShipQty end)
5

Could it just be a divide by zero error? Are you able to “review the server event log for more details”? You could try adding a check to the “when” statement to confirm the denominator is > 0.

1 Like
6

If I rewrite it as this (knowing it is bogus but still testing your formula):

select case when orderdtl.QuoteLine = 1 then 
		round((Shipdtl.OurInventoryShipQty + ShipDtl.OurJobShipQty) 
       / CAST(OrderDtl.OrderQty AS decimal) + (OrderDtl.OrderQty / 12.0), 0)
       else ShipDtl.OurInventoryShipQty + ShipDtl.OurJobShipQty end
from erp.orderdtl join erp.shipdtl on orderdtl.company=shipdtl.company and orderdtl.ordernum=shipdtl.ordernum and orderdtl.orderline=shipdtl.orderline
where shipdtl.ordernum=28747
order by orderdtl.ordernum desc

it works just fine without SQL error. I think I agree with @Asz0ka in that you need to do some isnull() checking on the two custom fields so that your denominator is not zero and give that a try.

The other thing would be to also cast the (PFI_INCHES_C / 12.0) to a decimal in case it’s having TYPE issues…

1 Like