CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
David Sagang
David Sagang

Reputation: 342

Arithmetic Overflow inside of a view

I have a t-sql view in which I query data from different tables. I use a UNION to grab all the data. 

Create view ViewName
(
   ID,
   Name,
   Budget,
   ...
)
SELECT 
   ID,
   Name,
   Budget,
   ...
 FROM Table1
 UNION
 SELECT
   ID,
   Name,
   Budget,
 FROM Table2

When I query the view, I have the error bellow. However I can select any part of the 2 statements separately without errors. How can I solve this problem?

Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type bigint.

Upvotes: 1

Views: 154

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

First you should identify which column produces error by commenting columns one by one:

SELECT 
   ID
   --,Name
   --,Budget
   ...
FROM Table1
UNION
SELECT
   ID
   --,Name,
   --,Budget     -- putting commas at beginning is nice in such scenario
FROM Table2

Then you will have to deal with implicit conversion and incorrect data types.

From UNION:

The following are basic rules for combining the result sets of two queries by using UNION:

  • The number and the order of the columns must be the same in all queries.

  • The data types must be compatible.

When data types differ, the resulting data type is determined based on the rules for data type precedence. When the types are the same but differ in precision, scale, or length, the result is determined based on the same rules for combining expressions.

...

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned.

My guess is conversion from VARCHAR to BIGINT.

Upvotes: 1

Related Questions