December 3, 2014 at 5:25 am
Hi there,
I have a server with a database.
The server is mhsvi-datawarehouse. From this server I have a linked server to mhsvi-sql2012a\instance1.
The below script I want to include as a sub query - however when I run it I get the following error -
Msg 468, Level 16, State 9, Line 32
Cannot resolve the collation conflict between "Cyrillic_General_CI_AS" and "Latin1_General_CI_AS" in the concat operation.
Msg 468, Level 16, State 9, Line 32
Cannot resolve the collation conflict between "Cyrillic_General_CI_AS" and "Latin1_General_CI_AS" in the concat operation.
Now the collation on mhsvi-datawarehouse is Latin1_General_CI_AS.
The database I'm trying to get to on mhsvi-sql2012a is Cyrillic_General_CI_AS.
So I understand the error - but is there a way to resolve this please?
SELECT
CONCAT(CONVERT(varchar(5),LEFT(IMADD.Postcode, 5),101)
,
'/'
,
CASE
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END <=24 THEN 'A'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 25 AND 34 THEN 'B'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 35 AND 44 THEN 'C'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 45 AND 54 THEN 'D'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 55 AND 64 THEN 'E'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 65 AND 74 THEN 'F'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 75 AND 84 THEN 'G'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END >=85 THEN 'H'
ELSE '' END
) AS 'Small Cluster'
,CASE WHEN TYP.PriorityLevelID = '1' THEN 'ASB-A'
WHEN TYP.PriorityLevelID = '2' THEN 'ASB-B'
ELSE '' END
FROM[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[Cases] AS CAS
INNER JOIN
[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[CaseTypeStatuses] STA
ONCAS.CaseID = STA.CaseID
INNER JOIN
[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[CaseTypes] AS TYP
ONSTA.CaseTypeID = TYP.CaseTypeID
INNER JOIN
[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[CaseIndividualCache] AS CASI
ONCAS.CaseID = CASI.CaseID
ANDCASI.IndividualRole = '3'
INNER JOIN
[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[ImportedIndividuals] AS IMIND
ONCASI.ExternalID = IMIND.ExternalID
INNER JOIN
[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[ImportedIndividualAddresses] AS IMADD
ONIMIND.ImportedIndividualAddressID = IMADD.ImportedIndividualAddressID
WHERE CAS.DateReceived >= CONVERT(DATETIME, '2013-04-01 00:00:00', 102)
AND CAS.DateReceived <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102)
AND CAS.IsAborted = '0'
ORDER BY [Small Cluster]
December 3, 2014 at 5:30 am
Sure.
Take a look here for an example.
Unfortunately, it's heavy on CPU and slows things down quite a lot.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 3, 2014 at 5:53 am
Since the error occurred in the concat operation, you just need to make sure that all the things you are concatenating have the same collation. One way of doing that is by putting a [font="Courier New"]COLLATE xxx[/font] clause after each literal in that CONCAT statement, where xxx is the collation of the column [mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[ImportedIndividualAddresses].Postcode.
John
December 3, 2014 at 6:02 am
Excellent guys.
Placed it just in the CONCAT expression in the SELECT and the GROUP BY.
Thank yo so much - Revised Code below.
SELECT
CONCAT(CONVERT(varchar(5),LEFT(IMADD.Postcode, 5)collate DATABASE_DEFAULT)
,
'/'
,
CASE
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END <=24 THEN 'A'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 25 AND 34 THEN 'B'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 35 AND 44 THEN 'C'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 45 AND 54 THEN 'D'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 55 AND 64 THEN 'E'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 65 AND 74 THEN 'F'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 75 AND 84 THEN 'G'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END >=85 THEN 'H'
ELSE '' END
) AS 'Small Cluster'
,count(case when TYP.PriorityLevelID = 1 then 1 else null end) AS 'ASB_A'
,count(case when TYP.PriorityLevelID = 2 then 1 else null end) AS 'ASB_B'
FROM[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[Cases] AS CAS
INNER JOIN
[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[CaseTypeStatuses] STA
ONCAS.CaseID = STA.CaseID
INNER JOIN
[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[CaseTypes] AS TYP
ONSTA.CaseTypeID = TYP.CaseTypeID
INNER JOIN
[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[CaseIndividualCache] AS CASI
ONCAS.CaseID = CASI.CaseID
ANDCASI.IndividualRole = '3'
INNER JOIN
[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[ImportedIndividuals] AS IMIND
ONCASI.ExternalID = IMIND.ExternalID
INNER JOIN
[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[ImportedIndividualAddresses] AS IMADD
ONIMIND.ImportedIndividualAddressID = IMADD.ImportedIndividualAddressID
WHERE CAS.DateReceived >= CONVERT(DATETIME, '2013-04-01 00:00:00', 102)
AND CAS.DateReceived <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102)
AND CAS.IsAborted = '0'
GROUP BY
CONCAT(CONVERT(varchar(5),LEFT(IMADD.Postcode, 5)collate DATABASE_DEFAULT)
,
'/'
,
CASE
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END <=24 THEN 'A'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 25 AND 34 THEN 'B'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 35 AND 44 THEN 'C'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 45 AND 54 THEN 'D'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 55 AND 64 THEN 'E'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 65 AND 74 THEN 'F'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 75 AND 84 THEN 'G'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END >=85 THEN 'H'
ELSE '' END
)
ORDER BY [Small Cluster]
December 3, 2014 at 6:15 am
Excellent. By the way, you can avoid repeating that concatenation in the GROUP BY clause by using a CTE:
WITH CTE AS (
SELECT
CONCAT(CONVERT(varchar(5),LEFT(IMADD.Postcode, 5)collate DATABASE_DEFAULT),
'/',
CASE
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END <=24 THEN 'A'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 25 AND 34 THEN 'B'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 35 AND 44 THEN 'C'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 45 AND 54 THEN 'D'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 55 AND 64 THEN 'E'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 65 AND 74 THEN 'F'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END BETWEEN 75 AND 84 THEN 'G'
WHEN DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB)
THEN 0 WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB)
THEN 0 ELSE 1 END >=85 THEN 'H'
ELSE '' END
) AS [Small Cluster]
,count(case when TYP.PriorityLevelID = 1 then 1 else null end) AS 'ASB_A'
,count(case when TYP.PriorityLevelID = 2 then 1 else null end) AS 'ASB_B'
FROM[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[Cases] AS CAS
INNER JOIN
[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[CaseTypeStatuses] STA
ONCAS.CaseID = STA.CaseID
INNER JOIN
[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[CaseTypes] AS TYP
ONSTA.CaseTypeID = TYP.CaseTypeID
INNER JOIN
[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[CaseIndividualCache] AS CASI
ONCAS.CaseID = CASI.CaseID
ANDCASI.IndividualRole = '3'
INNER JOIN
[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[ImportedIndividuals] AS IMIND
ONCASI.ExternalID = IMIND.ExternalID
INNER JOIN
[mhsvi-sql2012a\instance1].[ReACT_Live].[dbo].[ImportedIndividualAddresses] AS IMADD
ONIMIND.ImportedIndividualAddressID = IMADD.ImportedIndividualAddressID
WHERE CAS.DateReceived >= CONVERT(DATETIME, '2013-04-01 00:00:00', 102)
AND CAS.DateReceived <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102)
AND CAS.IsAborted = '0'
)
SELECT
[Small Cluster]
,ASB_A
,ASB_B
FROM
CTE
GROUP BY
[Small Cluster]
John
December 3, 2014 at 6:47 am
Have you tried sending the whole query to the linked server, something like this?
SELECT *
FROM OPENQUERY([mhsvi-sql2012a\instance1],
'SELECT
CONCAT(CONVERT(varchar(5),LEFT(IMADD.Postcode, 5),101)
,
''/''
,
CASE
WHEN x.Age <= 24 THEN ''A''
WHEN x.Age BETWEEN 25 AND 34 THEN ''B''
WHEN x.Age BETWEEN 35 AND 44 THEN ''C''
WHEN x.Age BETWEEN 45 AND 54 THEN ''D''
WHEN x.Age BETWEEN 55 AND 64 THEN ''E''
WHEN x.Age BETWEEN 65 AND 74 THEN ''F''
WHEN x.Age BETWEEN 75 AND 84 THEN ''G''
WHEN x.Age >=85 THEN ''H''
ELSE '' END
) AS ''Small Cluster''
,CASE WHEN TYP.PriorityLevelID = ''1'' THEN ''ASB-A''
WHEN TYP.PriorityLevelID = ''2'' THEN ''ASB-B''
ELSE '' END
FROM [ReACT_Live].[dbo].[Cases] AS CAS
INNER JOIN [ReACT_Live].[dbo].[CaseTypeStatuses] STA
ON CAS.CaseID = STA.CaseID
INNER JOIN [ReACT_Live].[dbo].[CaseTypes] AS TYP
ONSTA.CaseTypeID = TYP.CaseTypeID
INNER JOIN [ReACT_Live].[dbo].[CaseIndividualCache] AS CASI
ON CAS.CaseID = CASI.CaseID
AND CASI.IndividualRole = ''3''
INNER JOIN [ReACT_Live].[dbo].[ImportedIndividuals] AS IMIND
ON CASI.ExternalID = IMIND.ExternalID
INNER JOIN [ReACT_Live].[dbo].[ImportedIndividualAddresses] AS IMADD
ON IMIND.ImportedIndividualAddressID = IMADD.ImportedIndividualAddressID
CROSS APPLY (
SELECT Age = DATEDIFF([year],IMIND.DOB, GETDATE()) - CASE
WHEN MONTH(GETDATE()) > MONTH(IMIND.DOB) THEN 0
WHEN MONTH(GETDATE()) = MONTH(IMIND.DOB) AND DAY(GETDATE()) >= DAY(IMIND.DOB) THEN 0
ELSE 1 END
) x
WHERE CAS.DateReceived >= CONVERT(DATETIME, ''2013-04-01 00:00:00'', 102)
AND CAS.DateReceived <= CONVERT(DATETIME, ''2014-03-31 00:00:00'', 102)
AND CAS.IsAborted = ''0''
ORDER BY [Small Cluster]')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply