Division and Fractions

[MFisher]

Why does this 4 divided by 3 result = 1.000 instead of 1.3333 ?

select cast(4/3 as decimal(8,4) )
from sysibm.sysdummy1

I need to add several fields together, then multiply the result by another field defined as 3,0 by 100 and put the result in another field.
All the fields are defined in DDS as packed numeric with 0 decimals

( ( Fld_A x Fld_B) + (Fld_C x Fld_D) ) all multiplied by ( 1 - ( Fld_E / 100) )

Example ... Fld_E needs to be converted from whole number 35 to .35 ( like 35 % )

Not complicated math, but can't find the correct syntax working with decimals.

EDIT: I think if I code FLOAT in the division, I will get what I want.

[Scott M]

it is because it is doing integer division resulting in an integer. Cast one of the values to decimal first or if a constant add '.0'. These two examples work to demonstrate

select cast(4.0/3 as decimal(8,4) )
from sysibm.sysdummy1;

select cast(cast(4 as decimal(8,4) ) / 3 as decimal(8,4) )
from sysibm.sysdummy1

In your example try changing 1 to 1.0 and 100 to 100.0, it will help but you may need other casting. Using my examples should help you get there.

[MFisher]

Thanks. Still trying to figure it out.

This is the actual logic without any CAST attempts. Field are all integers. Either 3,0 or 4,0 as specified in DDS

ROUND(((PBWD06 * PBWDM06) + (PBWE06 * PBWEM06))   * PBTBM06  * (1-(PBSP06)/100),0)

EDIT: I think this works.

ROUND(((PBWD06 * PBWDM06) + (PBWE06   * PBWEM06))  * PBTBM06  * (1-((cast(PBSP06 as dec(15,4)) )/100.0)),0)

[B.Hauser]

If a coulumn in a table/physical file is defined with Dec or Numeric (SQL) or P or S in DDS it is not interpreted as integer, even if there are no decimal positions defined.
So the Result can have decimal positions.
How ever if you use literals without decimals such as 4 / 3 then these are interpreted as integers ... and if in a calculation only integers are used the result will be an integer, i.e. decimal positions get truncated.
If you use literals with an decimal point, such as 4 / 3.0 the 3.0 is interpreted as decimal and the result will also have decimal positions.
If you calculate 4 / 3 * 1.0 the result will be 1. How ever if you caluclate 4 / 3.0 * 1 or 1.0 * 4/3 the result will be 1.3333333.
... as soon as a number is not an integer the result will be a FLOAT number.

[MFisher]

Thanks for the explanation.