Re: SQL precision problem

I suspect that if Justin had cast his last expression as he did in the common table expressions, he would have got the precision he wanted - maybe that's what you refer to, Chuck.

In STRSQL, one can get more precision a couple ways - one is brute force - just put a decimal point in either number, assuming literals. This then results in something with like maybe 31 digits. Lots of them.

The other is to use CAST or the DEC function

So

SELECT 5/2. from sysibm/sysdummy1

produces this ->

2.50000000000000000000000 - it ends somewhere!

Cheers
Vern

On 4/17/2016 5:12 PM, CRPence wrote:

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-+-'

..."