SQL: the conversion of the varchar value OVERFLOWED and int column

Hello experts,

I am trying to perform the following query but I don't know why I am having this error message.

SELECT 
P.SPoTInternalID,
P.Id,
EPM.NWSMGR_OLD_INTERNAL_Id
FROM mstt_schema.Project P
LEFT JOIN [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated] EPM
ON P.SPoTInternalID = EPM.NWSMGR_OLD_INTERNAL_Id
WHERE P.SPoTinternalID <> '' and  ([Version] = '1') and (Active = '1')

Open in new window


CONVERSION_FIELD.png
How should I cast my  fields in order to don't have the error message?

Thank you again for your help.
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dirk StraussSenior Full Stack DeveloperCommented:
The max value you can get in an INT is 2147483647
Have a look at the article: The conversion of the varchar value overflowed an int column
0
LD16Author Commented:
Thank you very much for your help and how can I get a workaround for this?

Thank you very much for your help.
0
Dirk StraussSenior Full Stack DeveloperCommented:
Look at the article, there is a solution marked in green. It seems like they suggested using BIGINT. See Difference between INT and BIGINT data type in Sql Server.

INT max = 2,147,483,647
BIGINT max = 9,223,372,036,854,775,807
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just to 'check the box', this begs the question: Why is a column that stores numbers defined as a varchar in the first place, especially if it is expected to participate in JOIN clauses with numeric columns?

varchar's take more memory to store the value, and it allows for non-numeric values to be entered that would cause a JOIN on a numeric column to fail.

In your code the below two columns need to be the same data type, and one is a varchar and the other is an int, so SQL Server attempted implicit conversion to CAST the varchar as an int, and one of the values was beyond the acceptable range of an int.  That's why the error message appeared.
P.SPoTInternalID = EPM.NWSMGR_OLD_INTERNAL_Id

Open in new window

Assuming the value cannot change, the only real options you have are to CAST the other numeric column as a varchar so that the JOIN is an 'apples-to-apples' comparison, or Dirk's solution above to CAST both columns as a BIGINT.
1
Vitor MontalvãoMSSQL Senior EngineerCommented:
What are the data types from the columns in the JOIN clause:
P.SPoTInternalID = EPM.NWSMGR_OLD_INTERNAL_Id
0
LD16Author Commented:
@Vitor:

Please find data type of both columns:

SPoTInternalID (varchar (255), null)
EPM.NWSMGR_OLD_INTERNAL_Id (int,(4),null)

Thank you very much for your help.
0
Scott PletcherSenior DBACommented:
Please, NO points to me for just this, points to Vitor, I'm just doing a quick follow-up on his posts since I've already read the q.

I added the table alias to the Version and Active columns just because it's proper form.  If either/both those columns are not from the "P" table, you'll have to adjust the LEFT JOIN conditions;

SELECT
P.SPoTInternalID,
P.Id,
EPM.NWSMGR_OLD_INTERNAL_Id
FROM mstt_schema.Project P
LEFT JOIN [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated] EPM
ON (P.SPoTInternalID <= 2147483647) AND P.SPoTInternalID = EPM.NWSMGR_OLD_INTERNAL_Id
WHERE (P.[Version] = '1') and (P.Active = '1')
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I was expecting those data types. SQL Server engine is making an implicit conversion from the varchar column to int so it can match with the int column. Since the other field is INT you can use Scott's solution to filter only integer values or if you want you can explicitly convert it to BIGINT:
CAST(P.SPoTInternalID AS BIGINT) = EPM.NWSMGR_OLD_INTERNAL_Id

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZberteocCommented:
If the SPoTInternalID column is not the PK for that table I would alter it as bigint:

alter table mstt_schema.Project alter column SPoTInternalID bigint

Open in new window

This way you can keep the query as is and without having to CAST or to use filters on the value.
0
Scott PletcherSenior DBACommented:
It's far more efficient to just filter out values that are too large rather than to convert the column, because the conversion makes any index seeks on the index impossible.

If the value never exceeds an int, there's also no real reason to waste another 4 bytes storing it, simply exclude impossible values from the join.
0
Pawan KumarDatabase ExpertCommented:
Converting the other column to VARCHAR is better rather than BIGINT , BIGINT will also fail if not today tomorrow definitely.  Check out the example below.

CAST(EPM.NWSMGR_OLD_INTERNAL_Id AS VARCHAR(MAX)) , With this you will not face overflow issues.

SELECT 
P.SPoTInternalID,
P.Id,
EPM.NWSMGR_OLD_INTERNAL_Id
FROM mstt_schema.Project P
LEFT JOIN [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated] EPM
ON P.SPoTInternalID = CAST(EPM.NWSMGR_OLD_INTERNAL_Id AS VARCHAR(MAX))
WHERE P.SPoTinternalID <> '' and  ([Version] = '1') and (Active = '1')

Open in new window


if your data is like below BIGINT will also fail.

/*------------------------
select cast('347386573465743265732865932465932' AS BIGINT)
------------------------*/

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

Open in new window


Also note that we should keep same data type of the columns that we need in JOINs else performance will be hampered as functions around columns in joins/where clause are not sargable and SQL Server will end up scanning the entire table.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
@Scott, looks like there are implicit conversions (varchar --> int) that will act as if you're using the CAST function explicitly.
0
LD16Author Commented:
Thank you very much for your help.

The following solution works for me:

CAST(P.SPoTInternalID AS BIGINT) = EPM.NWSMGR_OLD_INTERNAL_Id

Open in new window


Thank you again for your help.
0
Scott PletcherSenior DBACommented:
@Vitor:

That's true, a good point.  Assuming leading zeros are not allowed in the varchar column, the technique below would be a more efficient and far safer way to test the column.  LEN() is perhaps not sargable, but it's a very efficient check and won't require converting the column to a numeric value.

LEFT JOIN [RDITS-BI-QA1].[ECubeDWH].[dwh].[REF_NWS_Liste_Projects_Migrated] EPM
ON (LEN(P.SPoTInternalID) <= 9 OR (LEN(P.SPoTInternalID) = 10 AND P.SPoTInternalID <= '2147483647')) AND P.SPoTInternalID = EPM.NWSMGR_OLD_INTERNAL_Id
0
ZberteocCommented:
@Scott,

Tell us about the performance of that query in a scenario involving millions of rows. :)
0
Scott PletcherSenior DBACommented:
What method would perform better?  Moreover, the method will not fail, even if nonnumeric data beyond the length of an int value gets in the column.  Any method that casts to bigint will automatically fail in a large, non-numeric value is present in the column.
0
ZberteocCommented:
I understand that with this particular "design" there is not much you can do. :o)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.