## Sql for 8-year-old math…but be careful of implicit type conversion – sqlservercentral financial market futures

The Guardian published a story ( http://gu.com/p/494q6/stw) with a math problem, supposedly for Vietnamese eight-year-olds, where the solution required filling in a digit from 1-9 in each of 9 boxes (using each numeral only once), where the resulting total from the operations as specified by the other boxes equaled the specified value. (The : indicates a division operation usps shipping. Standard order of operations must be followed.)

Since the solution seems to require a brute-force sort of approach, I thought it would be fun to write a simple SQL script to generate the solution. It seemed like a Cartesian join would be a useful tool to generate the possible values to try.

Next, I decided to create a numbers table to hold the numerals 1-9. (I know there are lots of ways to create a number table without looping, but for readability I am intentionally using looping here.)

Essentially, we are joining to the numbers table 9 times, and using the ON criteria of the join to prevent numerals from being used more than once.

In the WHERE clause, we have simply stated the original problem, using a.n, b.n and so forth to represent the empty boxes we are trying to fill.

SQL does say the answer to this is 66. But if we use a calculator, we get a result of 67.5417! Yikes! If we had printed out our results and turned it in to the teacher we would have failed the assignment. What is going on?

This returns the result of 3 gender quiz boy or girl. Notice that @x is really a character string…and you can’t really perform an arithmetic operation on a character string. SQL was kind enough to automatically (implicitly) convert the string to a number for us before performing the calculation. Very convenient and intuitive, right?

What? Why is SQL trying to convert ‘1.1’ to an integer? It is obvious that this is a floating point value. Furthermore we haven’t indicated in our query that SQL ought to be working with integers instead of floating point values.

Well, actually, we have indicated that SQL ought to be working with integers **usa today** high school football rankings. SQL sees the "+ 2", and says "Hmm. 2 is an integer. Therefore the caller wants the operation to be performed on integers zloty usd. Therefore I must cast the string ‘1.1’ to an integer…but that results in an error". The type of integer is implied (hence the phrase "implicit type conversion").

This returns the result of 3.1. The only thing we did different here was to use 2.0 instead of 2. But this was enough to imply to SQL that we wanted to work with floating point values instead of integer, so then SQL’s implicit type conversion converted our string ‘1.1’ to the floating point value 1.1 as we desired.

What is more, when SQL performs an operation on two integers, if the result contains a fraction, the fractional portion is discarded **gold price 2016 forecast**. The result is NOT rounded. For example:

This returns a 0 stock connection. We know that 2/3 really equals .666667, and we know that if we were to round to the nearest integer the result of 2/3 would be 1. But for integer-based calculations SQL simply discards the fractional portion, so 0.666667 gets truncated to simply 0.

This returns 0.666666 But wait! Shouldn’t this be rounded to 0.666667? Well, even when working with floating point values SQL will not round unless we explicitly tell it to.

Nope *crude oil futures marketwatch*. This returns 0. Why? Because implicit type conversion indicates that the arithmetic operation is to be performed on integers (resulting in 0), and we then cast 0 to a floating point value.

• Rule 1: SQL will always try to determine the data type involved in each arithmetic operation, and will perform the calculation according to that type.

• Rule 3: SQL may increase the precision of the data type for the result of an operation. This may mean that you need to explicitly cast the result back to the desired data type to achieve the desired number of decimals.

• Rule 4: Explicit typecasting of the result of an arithmetic operation does not affect implicit type conversion of the values in the operation itself.

• Recommendation #1: It is best to explicitly indicate the type of each value involved in an arithmetic operation, either via data type of the column or variable, or via an explicit CAST or CONVERT. Failing to do this can lead to unexpected results

• Recommendation #2: For greatest safety, always use variables or columns instead of literal values gold usd. This will help eliminate unexpected type conversion surprises.

In our math problem, if we use a data type of int, SQL performs each operation as an integer operation. Effectively this means that each operation that would result in a decimal value has the fractional portion of the number discarded (not even rounded)…which results in the unexpected results our query returned. It makes sense that using an integer type would produce incorrect results.

It also makes sense that mixing data types within the calculation could produce unexpected results due to implicit type conversion stock __market futures__ cnn. But here we are carefully using consistent, explicit data types and still seeing strange results.

It would seem that a float data type, as well as all flavors of decimal data types should yield the correct results…but this is not the case.

To help depict this, I created this graph in Excel. The horizontal axis shows the precision of the decimal data type, while the vertical axis shows the scale. The color indicates the accuracy: black shows the overflow conditions that resulted in an error; green shows correct results; red shows the unexpected error results. (See attached Excel file for details.)

To read this chart, from the lower left it indicates that the following data types result in incorrect results: decimal(16,0), decimal(17,0), decimal(17,1), decimal(18,0), decimal(18,1), decimal(18.2), etc.

I set out to write a simple article about using SQL to solve a grade school math problem, and then I found myself deep into implicit data type conversion issues. Worse, I ended up getting stuck trying to figure out why 72 flavors of the decimal data type result in the incorrect results while 225 flavors work fine.

Microsoft says: "The decimal data type stores an exact representation of the number; there is no approximation of the stored value." This page also clarifies the distinction between exact and approximate data types. Yet in my testing it seems that some decimal types yield "approximate" results, and some yield inaccurate results.

SQL can be a good way to solve trial-and-error kinds of problems. But, be careful: SQL’s implicit type conversion can result in confusing or erroneous answers python binary. Getting in the habit of explicitly indicating the desired data type of each value involved in an arithmetic calculation will help avoid errors. Even so, sometimes mysteries arise.