Conversion failed when converting the nvarchar

Posted on 2014-12-17
Medium Priority
Last Modified: 2014-12-17
  I have two tables that I would like to join based on a column . However, the columns are different types. I get this error when I run the code 'Conversion failed when converting the nvarchar ' .

jobreference= (nvarchar(100),null)
job_code = int

Here is my query
select *  from DataJobs
LEFT JOIN posted_jobs 
ON jobreference= job_code

Open in new window

I tried casting
select *  from DataJobs
LEFT JOIN posted_jobs 
ON jobreference= CAST(@job_code,nvarchar(100))

Open in new window

but i get another error 'Must declare the scalar variable @job_code"
How do get this qry to work

Question by:SiemensSEN
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 200 total points
ID: 40505256
Lots of things to work on here...

(1) T-SQL needs to know what tables all JOIN columns are from, either  DataJobs.jobreference or posted_jobs.jobreference
(2)  Your JOIN only references one column, as the CAST includes a variable, so it effectively does not JOIN both tables.
  jobcode is a column (I'm guessing), and @jobcode is a variable.  Big difference.
(3)  CAST(something as nvarchar(100)), not CAST(something, nvarchar(100))
(4)  fyi SELECT * from a two-table join will return all columns in BOTH tables.  Verify that that's what you want.
LVL 18

Accepted Solution

Simon earned 1800 total points
ID: 40505259
select *  from DataJobs
LEFT JOIN posted_jobs
ON convert(int,DataJobs.jobreference)= posted_jobs.job_code

This should work if the the datajobs table contains the jobreference column.

Author Closing Comment

ID: 40505275
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

LVL 66

Expert Comment

by:Jim Horn
ID: 40505283
btw the answer you chose as the accepted solution will fail if any value in Datajobs.jobreference cannot be converted to an integer, e.g.
SELECT CONVERT(int, 'banana') 

Open in new window

LVL 18

Expert Comment

ID: 40505302
That's true. I was asssuming that the job reference is a varchar representation of a number. I find that I often make assumptions about what the OP is working with: I also made a guess as to which table contained each of the join columns. A trade-off between asking for more information and giving a quick answer that is likely, but not guaranteed to work.
LVL 66

Expert Comment

by:Jim Horn
ID: 40505307
Fair enough.  There are a lot of problems with the asker's T-SQL, so I went the route of flushing out everything I saw.

Featured Post

How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question