On 11-Feb-2016 15:50 -0600, Justin Taylor wrote:
I'm trying to figure out how SQL arithmetic involving decimals is
giving an integer result. I've put together a sample query that
replicates my question.
with Values as
( select cast(1.5267 as decimal(31,4)) as A
, cast(1.6 as decimal(12,4)) as B
from SYSIBM.SYSDUMMY1
)
select Values.*
, (A - B) / B * 100.00 as RESULT
from Values;
This gives: 1.5267 1.6 0
The RESULT column has a type of decimal(31,2), but the value is
zero. With a precision of 2, RESULT should actually be -4.58.
The above discussion was already /answered/ and the OP resolved their
issue, but seems nobody mentioned the ability to override the method SQL
uses to decide what precision will be used to perform intermediate
calculations of numeric expression.
The default rules [best I can recall, long referred-to in the lab as
the /precision reduction/ rules] for determining the precision for the
operators, would have effected a reduction in the scale [portion of the
precision] being maintained for the intermediate results of the full
numeric expression; the division portion of the expression effected a
loss of all precision to the right of the decimal point [the scale],
thus effectively producing an integer result of zero [for which
multiplication by any constant is still zero].
There is a simple and oft acceptable means to prevent the loss of
precision in the intermediate calculation; i.e. set the Minimum Divide
Scale to greater than the default of zero. AFaIK there is still no
capability via Start Interactive SQL (STRSQL), but both the Run SQL
Statement (RUNSQLSTM) script processor and the Run SQL (RUNSQL)
single-statement processor have a Decimal Result Options (DECRESULT)
parameter, with the third element being the Minimum Divide Scale defined
as "The minimum divide scale (number of decimal positions to the right
of the decimal point) that should be returned for both intermediate and
result data types" for which the command parameter element defaults to
zero. At least some of the SQL pre-compiler commands were updated to
have that same parameter added; e.g. Create SQL ILE RPG Object (CRTSQLRPGI).
For the command invocations; e.g. the following specification would
have enable achieving the expected result in the given example:
DECRESULT(*n, *n, 4)
Note: See also the "Maximum Precision" and "Maximum scale" settings
in the command parameter DECRESULT(), the first and second elements,
respectively. JDBC and ODBC should also have equivalent capabilities
for achieving such modified settings.
For SQL program sources, the SET OPTION statement; e.g. the following
specification would have enable achieving the expected result in the
given example:
SET OPTION DECRESULT = (31, 31, 4) ;
[
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzsoption.htm]
"...
.-,-------------------------------------.
V |
>>-SET OPTION---+-ALWBLK = --alwblk-option------------+-+--><
+-ALWCPYDTA = --alwcpydta-option------+
...
+-DECRESULT = --decresult-option------+
...
...
decresult-option
.-31-----------.
|-(-+-max-precision+-+--------------------------------------------+-)-|
| .-31--------. |
'-,-+-max-scale-+-+------------------------+-'
| .-0----------------. |
'-,-+-min-divide-scale-+-'
..."
midrange.com
