Join Fails between two varchar(22) fields with the same collation.

I am trying to link views  through an inner join both  fields both are varchar(22) but the join only works on two records.  Yet when I use the value from o the field from one view to search in the other it finds it, no problem.  I checked the collation in both tables for the join fields and they are both the same" "SQL_Latin1_General_CP1_CI_AS"  .  One of the tables is brought over as a view through openv query from mysql.

here is the code fro the open query:

SELECT     leg_id, CONVERT(varchar(10), leg_date) AS Leg_Date, LTRIM(RTRIM(CONVERT(varchar(22), Link))) AS Link, LEFT(CONVERT(varchar(10), Sorian_Num), 7) 
                      AS Sorian
FROM         OPENQUERY(tsr, 'select * from `xxxxxxx`.`TS_Link2_Charts_Sorian`') AS TS_Notes

Open in new window


here is the actual join:
SELECT     dbo.MICU_Times_Analysis.PRID, dbo.MICU_Times_Analysis.Date_Line, dbo.MICU_Times_Analysis.Link, dbo.Sorian_Confirmed.Link AS Expr1
FROM         dbo.MICU_Times_Analysis
JOIN           dbo.Sorian_Confirmed ON dbo.MICU_Times_Analysis.Link = dbo.Sorian_Confirmed.Link
WHERE     (dbo.MICU_Times_Analysis.AtDest >= '3/1/2018')

Open in new window


I am stumped
LVL 5
WillOwnerAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
I do like Nakul's observation about tempdb, and using collate.

But would use an accent insensitive collation - a binary preferably,
try COLLATE SQL_Latin1_General_CP850_Bin
and that date needs to be '20180103'

the other possibility is non-printable characters

would be worthwhile checking the two columns dbo.MICU_Times_Analysis.Link and dbo.Sorian_Confirmed.Link

might want to limit the selections, but could be revealing to see the link in pairs...

select Link, 'A' as src, len(rtrim(ltrim(link))) as leng, patindex('%[^'+char(32)+'-'+char(126)+']%',link collate SQL_Latin1_General_CP850_Bin) as chr
from dbo.MICU_Times_Analysis
union all 
select Link, 'B' as src, len(rtrim(ltrim(link))) as leng, patindex('%[^'+char(32)+'-'+char(126)+']%',link collate SQL_Latin1_General_CP850_Bin) as chr
from dbo.Sorian_Confirmed
order by 1,2

Open in new window

2
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>but the join only works on two records.
So .. this would imply that the JOIN works, but somehow the values are difference such that they are not joining.  

<wild guess>
Any leading or trailing spaces that can be removed via LTRIM(RTRIM()) ?
0
 
WillOwnerAuthor Commented:
Thanks for the reply Jim: Actually tried ltrim(trim on both sides didn't work.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
WillOwnerAuthor Commented:
Here is the real kicker a vlookup in excel is a 100% match.  I am dumbfounded!
0
 
ste5anSenior DeveloperCommented:
First of all: Use unambiguous date literals. E.g. '20180301'.
Then use table alias names to increase readability.

Did you look at your data?

SELECT TOP ( 1000 ) A.PRID ,
                    A.Date_Line ,
                    A.Link AS ALink ,
                    C.Link AS CLink
FROM   dbo.MICU_Times_Analysis A
       FULL JOIN dbo.Sorian_Confirmed C ON A.Link = C.Link
WHERE  A.AtDest >= '20180301';

Open in new window


and

SELECT TOP ( 1000 ) A.Link AS ALink ,
                    C.Link AS CLink
FROM   dbo.MICU_Times_Analysis A
       FULL JOIN dbo.Sorian_Confirmed C ON A.Link = C.Link;

Open in new window

0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
What is the collation of the tempdb on the server where the query with the JOIN is executed?

When you bring over data via OPENQUERY in the view, I suspect it is using tempdb as a temporary storage which would cause problems if it is on a different collation. To confirm, you can actually specify a collation explicitly in the JOIN

SELECT dbo.MICU_Times_Analysis.PRID, dbo.MICU_Times_Analysis.Date_Line, dbo.MICU_Times_Analysis.Link, dbo.Sorian_Confirmed.Link AS Expr1
FROM   dbo.MICU_Times_Analysis
JOIN   dbo.Sorian_Confirmed ON dbo.MICU_Times_Analysis.Link COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Sorian_Confirmed.Link COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE  (dbo.MICU_Times_Analysis.AtDest >= '3/1/2018')

Open in new window


If the tempdb collation does turn out to be a problem, I would experiment changing the view's SELECT statement to COLLATE the data columns and then you would not need to worry about specifing COLLATE in every query where the view is consumed.
2
 
ste5anSenior DeveloperCommented:
For checking the collations:

EXECUTE sys.sp_MSforeachdb 'SELECT ''?'', CAST(DATABASEPROPERTYEX(''?'',''collation'') AS VARCHAR(255));';

Open in new window

and

EXECUTE sp_msforeachdb '
	SELECT	''?'' ,
			OBJECT_NAME(C.object_id) ,
			C.name ,
			C.collation_name
	FROM	?.sys.columns C;
';

Open in new window

1
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Out of curiosity - what was it? Was it the collation of the tempdb or was it an unprintable character?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.