Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.07 seconds. Powered By: Snitz Forums 2000