Arithmetic overflow error converting expression to data type smallint

Question

Arithmetic overflow error converting expression to data type smallint

water490

SSChasing Mays

Points: 647
More actions
December 13, 2021 at 7:21 pm

#3963194

Hi everyone
I am getting error
Arithmetic overflow error converting expression to data type smallint
The calculation I am doing is producing very small numbers like 0.0000005328.
How can I fix the calculation so it produces correctly? I am guessing I have to typecast the calculation so it works properly because smallint is definitely wrong. I am not sure where it is getting the smallint from because the original CTE doesn't explictly state it. It is probably something that is assumed by SQL Server.
Any suggestions would be much appreciated.
Thank you

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24363 More actions · Answer 1

Could you post your code and sample data and DDL?

You are doing something that the query optimizer thinks will be a SMALLINT, but that being said, 0.0000005328 WILL successfully cast to smallint without any problems. You can verify this by running:

SELECT CAST(0.0000005328 AS SMALLINT)

The problem is LIKELY that you have some other column that is causing the problem OR you have some large value coming out.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

water490 wrote:

Hi everyone
I am getting error
Arithmetic overflow error converting expression to data type smallint
The calculation I am doing is producing very small numbers like 0.0000005328.
How can I fix the calculation so it produces correctly? I am guessing I have to typecast the calculation so it works properly because smallint is definitely wrong. I am not sure where it is getting the smallint from because the original CTE doesn't explictly state it. It is probably something that is assumed by SQL Server.
Any suggestions would be much appreciated.
Thank you

There are a whole lot of things that implicitly occur in code when you haven't explicitly defined things. We can't actually tell you how to fix "it" because you haven't posted the code that's causing the error.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

water490 SSChasing Mays Points: 647 More actions · Answer 3

thanks for the reply.

here is the code:

The column NEAR_TERM_STRIKE contains integers.

Even after putting the cast command I still get the same error. There is no other column in the query that is producing such small numbers. I have been building my query with multiple CTEs and after each CTE is done I test the output and no issues. The only exception is below code. I am getting errors ONLY when this block is run so I think the error is here.

SELECTT1.UNDERLYING_SYMBOL, 
T1.QUOTE_DATE,
T1.NEAR_TERM_STRIKE,
T1.OPTION_TYPE,
CASE
WHEN T1.NEAR_TERM_STRIKE = T2.MIN_STRIKE 
THEN CAST((LEAD(T1.NEAR_TERM_STRIKE) OVER (PARTITION BY T1.UNDERLYING_SYMBOL, T1.QUOTE_DATE ORDER BY T1.NEAR_TERM_STRIKE) - T1.NEAR_TERM_STRIKE) / 
     (T1.NEAR_TERM_STRIKE * T1.NEAR_TERM_STRIKE) * EXP(T3.NEAR_TERM_TIME * T4.NEAR_TERM_RATE) * T1.MIDPOINT_PRICE AS NUMERIC(18,18))
WHEN T1.NEAR_TERM_STRIKE = T2.MAX_STRIKE 
THEN CAST((T1.NEAR_TERM_STRIKE - LAG(T1.NEAR_TERM_STRIKE) OVER (PARTITION BY T1.UNDERLYING_SYMBOL, T1.QUOTE_DATE ORDER BY T1.NEAR_TERM_STRIKE)) /
 (T1.NEAR_TERM_STRIKE * T1.NEAR_TERM_STRIKE) * EXP(T3.NEAR_TERM_TIME * T4.NEAR_TERM_RATE) * T1.MIDPOINT_PRICE AS NUMERIC(18,18))
ELSE CAST((((LEAD(T1.NEAR_TERM_STRIKE) OVER (PARTITION BY T1.UNDERLYING_SYMBOL, T1.QUOTE_DATE ORDER BY T1.NEAR_TERM_STRIKE)) - 
      (LAG(T1.NEAR_TERM_STRIKE) OVER (PARTITION BY T1.UNDERLYING_SYMBOL, T1.QUOTE_DATE ORDER BY T1.NEAR_TERM_STRIKE))) / 2.0) / 
  (T1.NEAR_TERM_STRIKE * T1.NEAR_TERM_STRIKE) * EXP(T3.NEAR_TERM_TIME * T4.NEAR_TERM_RATE) * T1.MIDPOINT_PRICE AS NUMERIC(18,18))
END AS STRIKE_CONTRIBUTION
FROMOPTION_LIST_NEAR_TERM_CTE AS T1 INNER JOIN 
NEAR_TERM_STRIKE_MIN_MAX_CTE AS T2 ON 
T1.UNDERLYING_SYMBOL = T2.UNDERLYING_SYMBOL AND 
T1.QUOTE_DATE = T2.QUOTE_DATE INNER JOIN 
TIME_CTE AS T3 ON T1.UNDERLYING_SYMBOL = T3.UNDERLYING_SYMBOL AND T1.QUOTE_DATE = T3.QUOTE_DATE INNER JOIN 
DISCOUNT_RATE_CTE AS T4 ON T1.QUOTE_DATE = T4.QUOTE_DATE

water490 SSChasing Mays Points: 647 More actions · Answer 4

I completely understand. I have posted sample code.

water490 SSChasing Mays Points: 647 More actions · Answer 5

this suggestion does work

SELECT CAST(0.0000005328 AS NUMERIC(18,18)) FROM TIME_CTE

it produces:

0.000000532800000000

not sure why it doesn't work in my CTE posted.

water490 SSChasing Mays Points: 647 More actions · Answer 6

I modified the query so i look at part piece by piece. the problem is with calculation

1.0 / (T1.NEAR_TERM_STRIKE * T1.NEAR_TERM_STRIKE)

the values for NEAR_TERM_STRIKE range from about 1000 to 3000.

I tried this and still doesn't work

CAST(T1.NEAR_TERM_STRIKE * T1.NEAR_TERM_STRIKE AS int)

How can I fix my query?

This reply was modified 2 years, 9 months ago by water490.

water490 SSChasing Mays Points: 647 More actions · Answer 7

i fixed the problem

i changed the original column datatype from smallint to int and now the error is gone.

thank you everyone!

ratbak SSC Eights! Points: 939 More actions · Answer 8

You haven't posted your CTEs, just a query that appears to reference four CTEs (OPTION_LIST_NEAR_TERM_CTE, NEAR_TERM_STRIKE_MIN_MAX_CTE, TIME_CTE, & DISCOUNT_RATE_CTE), so we can't know either.

Can you post DDL for the tables referenced, & assuming the four objects suffixed as CTEs really are, the portion of the query preceding the SELECT that defines those CTEs (obfuscated/anonymized as needed to protect confidential information)? And sample data that reproduces the error?

How to post code problems

Mr. Brian Gale SSC-Insane Points: 24363 More actions · Answer 9

My opinion - I would try your query as just a SELECT without the CAST and see what you get back, as well as selecting all values in that CTE.

If it does work, you can work with the data to try to figure out which one is causing you problems. It is likely as Jeff said - an implicit conversion causing problems. For example, the calculation:

T1.NEAR_TERM_STRIKE * T1.NEAR_TERM_STRIKE

MIGHT be doing a SMALLINT * SMALLINT which is likely going to blow up into an INT. For example, the following query will give you an error:

DECLARE @test SMALLINT = 100
SELECT CAST(@test*@test*@test AS INT)

because @test-2*@test*@test will exceed a smallint even though it will easily fit into an INT. To fix it, you would need to either change the datatype of @test-2 to INT OR CAST one of those to an INT (or larger) such as:

DECLARE @test SMALLINT = 100
SELECT CAST(CAST(@test AS INT)*@test*@test AS INT)

The reason being is implicit conversion. A SMALLINT * SMALLINT * SMALLINT will result in a SMALLINT in on the SQL side. BUT if you do INT * SMALLINT * SMALLINT, SQL will implicitly convert the whole thing to an INT.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Michael L John One Orange Chip Points: 26551 More actions · Answer 10

0.0000005328 is not an integer. When you cast or convert it, it will be 0. The decimal portion is truncated. The largest number a Numeric (18,18) can hold is 0.999999999999999999.

It appears you are making your code work via trial and error, without regard to the accuracy of your calculations.

I think you need to examine your data, and select the proper data types. I do not know the nature of your system and data, but I shudder to think what kind of disaster may occur with clearly bad calculations such as this.

https://docs.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-ver15

This reply was modified 2 years, 9 months ago by Michael L John.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/