Answers to: Scale used when evaluating a simple expression in SQLhttp://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql<p>Hi,</p>
<p>I am trying to understand how SQL Anywhere decides on the scale when evaluating an expression.</p>
<p>Take this for example:</p>
<div class="codehilite"><pre><span class="nb">select</span> <span class="mf">5.922733</span> <span class="o">*</span> <span class="mi">30</span><span class="o">/</span><span class="mi">365</span> <span class="n">from</span> <span class="n">dummy</span>
</pre></div>
<p>Returns => 0.4867999726 (dbisql)</p>
<p>It looks like it's using 10 decimal places. If I used Windows (Standard) calc the above expression returns 0.4867999726027397.</p>
<p>If I do this however</p>
<div class="codehilite"><pre><span class="nb">select</span> <span class="mi">30</span><span class="o">/</span><span class="mi">365</span> <span class="n">from</span> <span class="n">dummy</span>
</pre></div>
<p>Returns => 0 (dbisql)</p>
<p>Windows calc returns 0.0821917808219178 for 30/365.</p>
<p>My 2 questions are.</p>
<ol>
<li>Does SQL Anywhere use 10 decimal
places by default? </li>
<li>Why does it
return 0 for: select 30/365 from
dummy</li>
</ol>
<p>Thanks,</p>
<p>Shane</p>enWed, 02 Oct 2013 03:58:10 -0400Comment by Mark Culp on Volker Barth's questionhttp://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18641<p>Done. Thanks for pointing it out.</p>Mark CulpWed, 02 Oct 2013 03:58:10 -0400http://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18641Comment by Shane McEneaney on Volker Barth's answerhttp://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18634<p>Thanks again Volker for taking the time.</p>Shane McEneaneyTue, 01 Oct 2013 15:01:35 -0400http://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18634Comment by Volker Barth on Mark Culp's questionhttp://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18633<p><a href="/users/239/mark/">@Mark</a>: Can you adjust the link on significant digits, please? It does not seem to work.</p>Volker BarthTue, 01 Oct 2013 14:44:03 -0400http://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18633Comment by Volker Barth on Shane McEneaney's questionhttp://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18615<p>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.)</p>
<p>But clearly Mark's explanations on the rules of fixed point arithmetics are more appropriate to your particular concern.</p>Volker BarthTue, 01 Oct 2013 08:13:21 -0400http://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18615Comment by Mark Culp on Shane McEneaney's questionhttp://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18614<p>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 <a href="http://en.wikipedia.org/wiki/Significant_figures">significant digits</a>. 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.</p>Mark CulpTue, 01 Oct 2013 08:08:44 -0400http://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18614Comment by Shane McEneaney on Volker Barth's answerhttp://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18610<p>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.</p>Shane McEneaneyTue, 01 Oct 2013 06:52:57 -0400http://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18610Comment by Shane McEneaney on Mark Culp's answerhttp://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18609<p>Many thanks for the response. </p>
<p>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.</p>
<div class="codehilite"><pre><span class="nb">select</span> <span class="mf">5.922733</span> <span class="o">*</span> <span class="mi">30</span><span class="o">/</span><span class="mi">365</span> <span class="n">from</span> <span class="n">dummy</span>
</pre></div>
<p>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.</p>
<p>Thanks again,</p>
<p>Shane</p>Shane McEneaneyTue, 01 Oct 2013 06:51:30 -0400http://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18609Answer by Volker Barthhttp://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql/18597<p>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):</p>
<p><a href="http://www.sybase.com/content/1027471/mixeddomain.pdf">iAnywhere Solutions Technical Whitepaper: Mixed-Typed Comparisons In Adaptive Server Anywhere 9</a></p>
<p>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.</p>Volker BarthMon, 30 Sep 2013 15:26:52 -0400http://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql/18597Answer by Mark Culphttp://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql/18596<p>The scale that is used by SQLAnywhere for a calculation is dependent on the inputs to the calculations.</p>
<p>For you examples:</p>
<ul>
<li>30/365 : here both inputs are integers to the calculation is done using integral arithmetic hence the resultant value of 0.</li>
<li>5.922733 * 30/365 : this one is a bit more complicated to explain so lets go through the process:<ul>
<li>first you need to realize that the computation uses the <a href="http://dcx.sybase.com/index.html#sa160/en/dbreference/operator-precedence-wsqlref.html">normal order-of-operation rules</a>, so this implies that 5.922733 * 30 is computed first.</li>
<li>The result of computing 5.922733 * 30 is a numeric( 9, 6 ) - i.e. scale of 6 - since the inputs have a total scale of 6 ( i.e. 6 plus 0 )</li>
<li>SQLA will then do the division by 365 increasing the scale by a reasonable amount (*) to keep the precision of the result. In this case the result is a numeric( 13, 10 )</li>
</ul>
</li>
</ul>
<p>HTH to explain. You can use the <a href="http://dcx.sybase.com/index.html#sa160/en/dbreference/exprtype-function.html">exprtype()</a> function if you want to determine what the resultant type of a calculation would be.</p>
<p>(*) The resultant scale is a simple computation left as an exercise for the reader - I didn't bother to look it up!</p>Mark CulpMon, 30 Sep 2013 13:26:24 -0400http://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql/18596Comment by John Smirnios on Shane McEneaney's questionhttp://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18595<p>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.</p>John SmirniosMon, 30 Sep 2013 13:19:38 -0400http://sqlanywhere-forum.sap.com/questions/18593/scale-used-when-evaluating-a-simple-expression-in-sql#18595