Error when trying to do join on two tables

Question

Post reply

Error when trying to do join on two tables

donnelcyril

SSC Veteran

Points: 215
More actions
November 23, 2008 at 5:55 pm

#196571

Hi,
Please help me with the following query:
It is giving this weird error giving me no idea where to start looking:
Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '110736663384' overflowed an int column. Maximum integer value exceeded.
My query is as below:
SELECT *
FROM ((table1 AS t1 INNER JOIN table2 AS t2 ON
t1.ID = t2.ID)
INNER JOIN table3 AS t3
ON t2.id2 = t3.id )
Just a rough figures of records on the tables I have used.
Table1 500 records
Table2 > 210000 records
Table3 > 300000 records

Viewing 5 posts - 1 through 4 (of 4 total)

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

Tom257 SSChampion Points: 11543 More actions · Answer 1

Some suggestions:

Try SELECT T1.ID, T2.ID, T3.ID

instead of SELECT * - if it now works, add columns back in until you find the problem column

and Try adding a WHERE clause

WHERE t1.ID = [known value]

If you still can't work out where the problem may lie, then can you post the CREATE TABLE statements for table1, table2 and table3.

winash SSChampion Points: 11446 More actions · Answer 2

As Tom mentioned - could you post the table structures?

It looks like one of the ID columns in one of the tables you are joining on is of varchar data type and has a value greater than the permissible int value and is joining to a column of integer data type...

For e.g.:

DECLARE @Tab1 TABLE(ID int)

DECLARE @Tab2 TABLE(ID varchar(30))

INSERT @Tab1(ID)

SELECT 10

UNION ALL

SELECT 20

UNION ALL

SELECT 30

INSERT @Tab2(ID)

SELECT '10'

UNION ALL

SELECT '20'

UNION ALL

SELECT '30'

UNION ALL

SELECT '110736663384'

SELECT * FROM @Tab1

SELECT * FROM @Tab2

SELECT T1.ID,T2.ID FROM @Tab1 T1 INNER JOIN @Tab2 T2 ON

T1.ID = T2.ID

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Please don't cross-post. There are now two different discussions going on about the same topic. Please continue discussion at the other location:

http://www.sqlservercentral.com/Forums/Topic607252-1291-1.aspx?Update=1

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

vaibhav kumar-474069 Newbie Points: 3 More actions · Answer 4

SELECT T1.ID,T2.ID FROM @Tab1 T1 INNER JOIN @Tab2 T2 ON

convert(varchar,T1.ID) = convert(varchar,T2.ID)

Try using this query by converting both joining coloumns

to varchar