# SQL Help on Decimal/Integer Math

**URL:** <https://www.epiusers.help/t/sql-help-on-decimal-integer-math/67128>\
**Category:** Epicor ERP 10\
**Tags:** sql\
**Created:** [May 12, 2020, 5:43pm UTC](https://www.epiusers.help/t/sql-help-on-decimal-integer-math/67128 "2020-05-12T17:43:20Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jason\_Woods](https://www.epiusers.help/user_avatar/www.epiusers.help/jason_woods/32/80101_2.png) [@Jason\_Woods](https://www.epiusers.help/u/Jason_Woods)\
**Post date:** [May 12, 2020, 5:43pm UTC](https://www.epiusers.help/t/sql-help-on-decimal-integer-math/67128/1 "2020-05-12T17:43:20Z")

</div>

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

```auto
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:

```auto
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:

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

```

---

<div class="post-metadata">

**Author:** ![MikeGross](https://www.epiusers.help/user_avatar/www.epiusers.help/mikegross/32/14500_2.png) [@MikeGross](https://www.epiusers.help/u/MikeGross)\
**Post date:** [May 12, 2020, 6:00pm UTC](https://www.epiusers.help/t/sql-help-on-decimal-integer-math/67128/2 "2020-05-12T18:00:38Z")

</div>

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

> [@Jason\_Woods](#):
>
> 500/(OrderDtl.PFI\_FEET\_c + OrderDtl.PFI\_INCHES\_c / 12)

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

> [@Jason\_Woods](#):
>
> 500/(OrderDtl.PFI\_FEET\_c + (OrderDtl.PFI\_INCHES\_c / 12))

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

---

<div class="post-metadata">

**Author:** ![ckrusen](https://www.epiusers.help/user_avatar/www.epiusers.help/ckrusen/32/50584_2.png) [@ckrusen](https://www.epiusers.help/u/ckrusen)\
**Post date:** [May 12, 2020, 6:57pm UTC](https://www.epiusers.help/t/sql-help-on-decimal-integer-math/67128/3 "2020-05-12T18:57:26Z")

</div>

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

```auto
(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](https://epiusers.help/uploads/default/original/3X/c/4/c4a949bcebd0f6f6fe614afe7c2c89665b4f1a67.png)

---

<div class="post-metadata">

**Author:** ![Jason\_Woods](https://www.epiusers.help/user_avatar/www.epiusers.help/jason_woods/32/80101_2.png) [@Jason\_Woods](https://www.epiusers.help/u/Jason_Woods)\
**Post date:** [May 12, 2020, 9:35pm UTC](https://www.epiusers.help/t/sql-help-on-decimal-integer-math/67128/4 "2020-05-12T21:35:13Z")

</div>

@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”.

```auto
(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)

```

---

<div class="post-metadata">

**Author:** ![Asz0ka](https://www.epiusers.help/letter_avatar_proxy/v4/letter/a/a698b9/32.png) [@Asz0ka](https://www.epiusers.help/u/Asz0ka)\
**Post date:** [May 13, 2020, 10:50am UTC](https://www.epiusers.help/t/sql-help-on-decimal-integer-math/67128/5 "2020-05-13T10:50:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![MikeGross](https://www.epiusers.help/user_avatar/www.epiusers.help/mikegross/32/14500_2.png) [@MikeGross](https://www.epiusers.help/u/MikeGross)\
**Post date:** [May 13, 2020, 11:50am UTC](https://www.epiusers.help/t/sql-help-on-decimal-integer-math/67128/6 "2020-05-13T11:50:28Z")

</div>

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

```auto
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…