[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

656 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