# Scale used when evaluating a simple expression in SQL

 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. Does SQL Anywhere use 10 decimal places by default? Why does it return 0 for: select 30/365 from dummy Thanks, Shane asked 30 Sep '13, 12:37 Shane McEneaney 66●3●3●9 accept rate: 0% 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. (30 Sep '13, 13:19) John Smirnios

 The scale that is used by SQLAnywhere for a calculation is dependent on the inputs to the calculations. For you examples: 30/365 : here both inputs are integers to the calculation is done using integral arithmetic hence the resultant value of 0. 5.922733 * 30/365 : this one is a bit more complicated to explain so lets go through the process: first you need to realize that the computation uses the normal order-of-operation rules, so this implies that 5.922733 * 30 is computed first. 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 ) 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 ) 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! answered 30 Sep '13, 13:26 Mark Culp 24.6k●9●138●294 accept rate: 41% 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. answered 30 Sep '13, 15:26 Volker Barth 38.8k●351●528●799 accept rate: 34% 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
 toggle preview community wiki:

### Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

question asked: 30 Sep '13, 12:37

question was seen: 1,991 times

last updated: 02 Oct '13, 03:58