Re: SQL precision problem

On 17-Apr-2016 17:31 -0500, Vernon Hamberg wrote:

On 17-Apr-2016 17:12 -0500, 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.

<<SNIP>> 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. <<SNIP>>

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. <<SNIP>>

I expect not. Perhaps write what you mean, in SQL code, instead of trying to use /words/; I do not understand what is the implication. The OP complained of an incorrect result, but seemed not to be bothered with the precision for the result [being typed as "decimal(31,2)"]. The chosen\effective resolution was to reduce the precision [but keeping the same scale] for the first literal defined in the CTE.

As I interpret what is alluded in the above quoted most recent snippet of reply, I expect there would have been _no positive effect_ for the OP, in so doing; i.e. casting the [result of the] entire expression would have yielded the same /wrong result/ [of zero], such that the unexpected result would have persisted, because the origin for the error was the loss of precision [per the scale being eroded to zero for the division operator] for an intermediate calculation. The original discussion thread can be reviewed here:
[http://archive.midrange.com/midrange-l/201602/threads.html#00340]

I merely was suggesting that, rather than individuals delving into the complexity of SQL precision rules [with which two posters clearly had\expressed some difficulties], they might do better, simply to use the setting [an OPTION] of the SQL feature that asks that the normal SQL precision rules be overridden to prevent the loss of scale. The OP had an expression using two numbers clearly defined with four fractional digits of precision, and the use of DECRESULT(*N *N 4) easily enough requests [without requiring much understanding by the user] that the SQL should maintain those four fractional digits in the intermediate result of a division; i.e. maintain four digits as the "Minimum Divide Scale". Having done so, the expression could have remained unchanged, while yielding the expected result [instead of the unexpected result of zero].