Problem with MAX function on bigint column

Question

Problem with MAX function on bigint column

Viewing 9 posts - 16 through 23 (of 23 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Bill Mell (9/3/2008)
Where else would it get the number from if not from a bigint column.
the number is to large for an int.
I would think the important issue would be whether or not the target is a bigint (which it is)
Bill

From an nvarchar column:

The conversion of the nvarchar value "2147486776" overflowed an int column. Maximum integer value exceeded.

Have you tried Grant's suggestion of running a snippet against the base table?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Bill Mell SSCommitted Points: 1621 More actions · Answer 2

I have to run the query against a join of two base tables.

The Datetimex, RecID and ID columns are in one table, the HandleID in another.

The tables are joined on another column (int) which is the primary key of the table with the HandleID in it.

That being said, the query also fails when run against the source tables.

Bill

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

But the issue is the error message:

"nvarchar value "2147486776" overflowed an int column"

If you're selecting into a bigint variable from a big int column, where is the nvarchar and the int column coming from? GSquared's test shows that the basic concepts you're dealing with are correct, so something else is in play.

"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

Bill Mell SSCommitted Points: 1621 More actions · Answer 4

I'll buy that there is something else at play.

but no nvarchar columns are even addressed in the query.

Why it fails converting an nvarchar to an int is my big question.

I was assuming that maybe the MAX function converted it to an nvarchar for the purposes of finding the max (though why I can't say).

There are nvarchar columns in both tables addressed, but the query doesn't address those columns in any way.

Bill

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Bill Mell (9/3/2008)
I have to run the query against a join of two base tables.
The Datetimex, RecID and ID columns are in one table, the HandleID in another.
The tables are joined on another column (int) which is the primary key of the table with the HandleID in it.
That being said, the query also fails when run against the source tables.
Bill

Forget the receiving variable for now. Try running the part of the query which is apparently causing the problem, prune it back to a minimum:

SELECT MAX(RecID) FROM basetable (NOLOCK) WHERE Datetimex = somevalue AND ID = someothervalue

Also check the data types of the join columns between the two tables - I'd put my money on this...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Bill Mell SSCommitted Points: 1621 More actions · Answer 6

It must be something bizarre like that.

I just selected the max(recid) from the table, and it is still in the int range . . . so the error must be relating to something else. I guess I'll just have to take all the pieces apart and figure out where that number is coming from . . .

I'm glad to know that at least the max function works, even if something else is messing it up.

Bill

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

I reckon your join columns are nvarchar and int...and there's an nvarchar value too big to implicitly cast as an int...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Bill Mell SSCommitted Points: 1621 More actions · Answer 8

Actually all of the joins are matched by type, so it is not caused by that.

I just figured out the problem.

the HandleID is actually a nvarchar column rather than an int.

Not doing any joins on it but when I put quotes around the value it magically worked.

Glad to know that there is an answer.

Thanks,

Bill

GSquared SSC Guru Points: 260824 More actions · Answer 9

Glad to see you got it working.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon