Sounds like "SET OPTION DECRESULT = (31, 31, 4) ;" would have been exactly what I needed, and a lot simpler than what I ended up using.
-----Original Message-----
From: CRPence [mailto:crpbottle@xxxxxxxxx]
Sent: Sunday, April 17, 2016 5:12 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL precision problem
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-+-'
..."
--
Regards, Chuck
midrange.com
