The division occurs before the DEC, so the result of dividing integers
is truncated to an integer in a. In b & c, you've used a decimal value
in the operation, so DB2 returns a decimal result (which then rounds to
the correct value..).

-----Original Message-----
From: Olsen Richard N.
[mailto:db2udbdba-ezmlmshield-x56783245.[Email address protected]
Sent: Friday, March 07, 2008 9:55 AM
To: LazyDBA Discussion
Subject: Results for division operation

SELECT INT(ROUND(DEC(BUDGETEDUNITS_0/BUDGET_MONTHS,5,2),0))
INTO MTHLYUNITS_0
FROM SYSIBM.SYSDUMMY1;
ELSE SET MTHLYUNITS_0 = 0;
END IF;



The text above is one section of a stored procedure that I am currently
writing (using DB2 7.1 z/OS). The variables BUDGETEDUNITS_0,
BUDGET_MONTHS, and MTHLYUNITS_0 are defined as SMALLINT.




In testing (using Stored Procedure Builder V8) I get the following
result.
a)
SELECT INT(ROUND(DEC(707/23,5,2),0))
AS MTHLYUNITS_0
FROM SYSIBM.SYSDUMMY1;
30



In other test (using SPUFI) I get the following results:
b)
SELECT INT(ROUND(DEC(707/23.00,5,2),0))
AS MTHLYUNITS_0
FROM SYSIBM.SYSDUMMY1;
31

c)
SELECT INT(ROUND(DEC(707/23.0,5,2),0))
AS MTHLYUNITS_0
FROM SYSIBM.SYSDUMMY1;
31

1) Why am I receiving a difference in responses? Shouldn't I also
receive 31 for a?
2) When performing division, do the dividend and divisor have to be
defined the same?
ex: smallint/smallint, int/int, etc





Richard N. Olsen

Database Administrator

SC Department of Disabilities & Special Needs

Phone: (803) 898-9811

Email: [Email address protected]






DB2 & UDB email list listserv db2-l LazyDBA home page