|
Hi, 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.
Thanks, Shane |
|
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! Many thanks for the response. I am still unclear about the scale used in the division part of the expression. I understand from Mark's post that 5.922733 * 30 is done first and the scale is 6. That makes sense. select 5.922733 * 30/365 from dummy I don't understand why a scale of 10 is chosen for the '/365' part. Mark mentioned that "SQLA will then do the division by 365 increasing the scale by a reasonable amount". How does it decide what is reasonable? The standard Windows calculator has a scale of 16 by default but can be extended using the Scientific option. Thanks again, Shane
(01 Oct '13, 06:51)
Shane McEneaney
Replies hidden
Comment Text Removed
Windows calculator is simply doing all of its calculations using doubles and then blindly displays all of the digits regardless of whether they are significant or not. SQLA only displays the significant digits. For example 1.1 / 365 will give five significant digits (1.1 has 1 and the reciprocal of 365 has 4 giving a total of 5); displaying any more is just misleading.
(01 Oct '13, 08:08)
Mark Culp
1
@Mark: Can you adjust the link on significant digits, please? It does not seem to work.
(01 Oct '13, 14:44)
Volker Barth
Done. Thanks for pointing it out.
(02 Oct '13, 03:58)
Mark Culp
|
|
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): iAnywhere Solutions Technical Whitepaper: Mixed-Typed Comparisons In Adaptive Server Anywhere 9 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. Thanks for your response Volker. I had a look at the Whitepaper. It discusses comparison of different data types. I can't find any mention of scale when perfoming division.
(01 Oct '13, 06:52)
Shane McEneaney
Replies hidden
Yes, as stated, the paper doesn't discuss arithmetic operations. However, it does discuss what types are used when using operands of different types, e.g. when using int and float, and I had thought that your question has had a broader scope. (And it would also tell that operations between int operands still return ints, not fractional values.) But clearly Mark's explanations on the rules of fixed point arithmetics are more appropriate to your particular concern.
(01 Oct '13, 08:13)
Volker Barth
Thanks again Volker for taking the time.
(01 Oct '13, 15:01)
Shane McEneaney
|
I believe the answer to #2 is that both of your numbers are assumed to be integers and therefore it is also assumed you are doing integer arithmetic. Try 30.0/365 instead.