Conversion failed when converting the nvarchar

Posted on 2014-12-17
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 50 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 450 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

726 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