Solved

Conversion failed when converting the nvarchar

Posted on 2014-12-17
6
157 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
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.
0
 
LVL 18

Accepted Solution

by:
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.
0
 

Author Closing Comment

by:SiemensSEN
ID: 40505275
Thanks
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 65

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

0
 
LVL 18

Expert Comment

by:Simon
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.
0
 
LVL 65

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.
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

829 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