SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Join on a field in CONVERT statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rachelb
Starting Member

3 Posts

Posted - 10/05/2012 :  08:46:42  Show Profile  Reply with Quote
Hello,

I have the following query:
SELECT		r.SPID,
			a.Id,
			o.Id,
			r.ContractId,
			r.CCVDate,
			r.PaymentType,
			r.CStatus,
			u.Id,
			r.BoardCV,
			r.Split,
			CAST(r.ContractID AS NVARCHAR) + '-' + CAST(r.repID AS NVARCHAR)
			
			
FROM #recon r
INNER JOIN SalesForceLocal.dbo.[User] AS u with (nolock) ON r.RepId = u.Employee_ID__c
LEFT JOIN salesforcelocal.dbo.Account AS a WITH (NOLOCK) ON r.SPID = a.SPID__c
LEFT JOIN SalesForceLocal.dbo.Opportunity AS o with (nolock) ON r.ContractId = o.Contract_ID__c


ContractID is an integer field in the #recon temp table I created. I need to create an external primary key, so I created concatenation of the RepId (which an int too) and ContractId. I'm having issues now because I need to use ContractId as a field to join onto the Opportunity table. Since I changed it to a nvarchar, it refuses to join now. I was just going to create another temp table and join it on there, but I know there has to be an easier way to do this. Any suggestions?

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/05/2012 :  09:00:59  Show Profile  Reply with Quote
I must admit I didn't follow 100% what your requirements are. You do seem to be joining to the Opportunity table using the integer ContractID column.

As I see it, your choices are:

1. Keep all three columns ContractID, RepID and the concatenated column and join on the ContractID column

2. Instead of concatenating to create a character column, use a composite primary key consisting of two columns - ContractID and RepID.

I would prefer the second option, for a variety of reasons.

As an aside, although it would probably make no difference in your specific case, a recommended best practice is to always specify the length for NVARCHAR columns - for example NVARCHAR(30)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000