Solved

Conversion failed when converting the nvarchar

Posted on 2014-12-17
6
147 Views
Last Modified: 2014-12-17
hello,
  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

Thanks
0
Comment
Question by:SiemensSEN
  • 3
  • 2
6 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 50 total points
Comment Utility
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.
0
 
LVL 18

Accepted Solution

by:
SimonAdept earned 450 total points
Comment Utility
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.
0
 

Author Closing Comment

by:SiemensSEN
Comment Utility
Thanks
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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

0
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
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.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now