Join on a field in CONVERT statement

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

All Forums

General SQL Server Forums

New to SQL Server Programming

Join on a field in CONVERT statement

Author

Topic

rachelb
Starting Member

3 Posts

Posted - 2012-10-05 : 08:46:42

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-05 : 09:00:59

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources