I am trying to understand how SQL Anywhere decides on the scale when evaluating an expression.
Take this for example:
select 5.922733 * 30/365 from dummy
Returns => 0.4867999726 (dbisql)
It looks like it's using 10 decimal places. If I used Windows (Standard) calc the above expression returns 0.4867999726027397.
If I do this however
select 30/365 from dummy
Returns => 0 (dbisql)
Windows calc returns 0.0821917808219178 for 30/365.
My 2 questions are.
asked 30 Sep '13, 12:37
The scale that is used by SQLAnywhere for a calculation is dependent on the inputs to the calculations.
For you examples:
HTH to explain. You can use the exprtype() function if you want to determine what the resultant type of a calculation would be.
(*) The resultant scale is a simple computation left as an exercise for the reader - I didn't bother to look it up!
FWIW, here's a white paper from Ivan T. Bowman on the subject - based on ASA 9 but I guess it should still apply (and I guess the rules for comparisons and arithmetic operations are similar):
Note, when one of the numeric operands is of a floating point type (float, real or double) - say, because you use a column of that type -, then the result is always of type double.
answered 30 Sep '13, 15:26