Error Converting Data Type nvarchar to binint

I have a query that failing with this error:

"Error converting data type nvarchar to bigint"

My line that's failing is this:
INNER JOIN Country ON ColMeta.xCountry = Country.countryID

ColMeta.xCountry is nvarchar(1000)
Country.countryID = bigint

Is there a way to convert this on the fly (here in the query) without having to modify the database tables?

Thanks
greddinAsked:
Who is Participating?
 
Nathan RileyConnect With a Mentor FounderCommented:
inner join country on colmeta.xcountry = cast(country.countryid as nvarchar)
0
 
BrandonGalderisiCommented:
Is colMeta.xcountry the NAME of the country?  If so, perhaps your country table contains a NAME as well and you could join on the name instead of the country.countryID


So:

INNER JOIN Country ON ColMeta.xCountry = Country.countryNAME
0
 
Kevin CrossChief Technology OfficerCommented:
What are the values of ColMeta.xCountry?

Think issue is most likely the field size if your data consists of all numeric/whole number values and no extraneous text.

You can try a cast, but may have to do some trimming first.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kevin CrossChief Technology OfficerCommented:
I would agree with Brandon as I double checked and size doesn't impact this with NVARCHAR.  I have seen it with some datatypes that it needs a numeric data type with same length, but I was able to cast NVARCHAR(1000) as long as the length of actual data strings are 19 characters or less.

If they are over that, then you have to use a different data type.

If you have string values, then find different column to match against like Brandon indicated.
0
 
Kevin CrossChief Technology OfficerCommented:
LOL, that will work too...
0
 
Kevin CrossChief Technology OfficerCommented:
Sometimes it is the simpliest ways that work best.  Gallitin's solution to focus on casting the BIGINT to NVARCHAR(1000) is a good approach.  Although the fact that the xcountry would not cast to BIGINT would state that it will probably not match up to your BIGINT countryid for at least one value which may be what you want anyway since you are using an INNER JOIN.

0
 
Nathan RileyFounderCommented:
Yeah don't know if it would work, but figured it was a place to start.  When I first got on here cast and convert were the hardest thing for me, but know I have a whole knowledge base from everyone helping me:)
0
 
greddinAuthor Commented:
ColMeta.xCountry is just a numeric value. Thanks.
0
 
greddinAuthor Commented:
Thanks to all who responded. But this is exactly what I was looking for.
0
 
Kevin CrossChief Technology OfficerCommented:
Greddin, glad Gallitin's solution worked for.  To solve the root of the issue though, I would check your data for xcountry value greater than 19 characters if you can't change datatype.  Spaces don't impact the cast, so it is actual data length.

SELECT xCountry
FROM ColMeta
WHERE LEN(LTrim(RTrim(xCountry))) > 19

Good luck!
0
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.

All Courses

From novice to tech pro — start learning today.