Collation conflict error - SQL Server Forums

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

Collation conflict error

Author

Topic

yhassan
Starting Member

8 Posts

Posted - 2007-10-24 : 06:33:51

Hi,

I am getting the following error message when trying to run a script:
"Cannot resolve collation conflict for equal to operation."

Any help on resolving this would be most appreciated, thanks!

my SQL code is as follows:

CREATE VIEW IP_SLAM_EXTRACT_1
AS
SELECT LEFT(a.provider,3) AS [Trust Code],
CASE WHEN a.practice_code='F86687' THEN 'F86723'
WHEN a.practice_code='F86733' THEN 'F86068'
WHEN a.practice_code='F86677' OR
a.practice_code='F86693' THEN 'F86675'
WHEN a.practice_code='F86714' OR
a.practice_code='F86129' THEN 'F86634'
WHEN a.practice_code='F86050' THEN 'Y00090'
WHEN a.practice_code='F86669' OR
a.practice_code='F86662' THEN 'F86702'
WHEN a.practice_code='F886051' THEN 'F86634'
WHEN a.practice_code='F86059' OR
a.practice_code='F86055' THEN 'F86060'
WHEN a.practice_code='F86690' THEN 'F86637'
WHEN a.practice_code='F86089' THEN 'F86060'
WHEN a.practice_code='F86717' OR
a.practice_code='F86728' THEN 'F86716'
WHEN a.practice_code='F86727' OR
a.practice_code='F86728' THEN 'F86721'
WHEN a.practice_code IS NULL AND
a.postcode NOT IN
(SELECT DISTINCT p.POSTCODE
FROM REPOSITORY..[Exeter PostCode]p) THEN 'Null_not_5NA'
WHEN a.practice_code IS NULL AND
a.postcode IN
(SELECT DISTINCT p.POSTCODE
FROM REPOSITORY..[Exeter PostCode]p) THEN 'Null_5NA'

ELSE a.practice_code END AS [GP Practice],
LEFT(
CASE WHEN a.treatment_function_code LIKE 'mis%' OR
a.treatment_function_code LIKE '?%' OR
a.treatment_function_code LIKE '#%' THEN
a.main_specialty_code
WHEN a.treatment_function_code ='' OR
a.treatment_function_code IS NULL AND
a.main_specialty_code IS NOT NULL THEN
a.main_specialty_code
WHEN a.treatment_function_code ='' OR
a.treatment_function_code IS NULL AND
a.main_specialty_code IS NULL THEN '999'
WHEN a.treatment_function_code LIKE '0%' THEN '999'
ELSE a.treatment_function_code END,3) AS [Specialty Code],
a.hrg_code_calculated AS [HRG Code],
a.local_patient_identifier as [Patient id Code],
a.patient_classification AS [Pat Class],
a.admission_method AS [Adm Method],
a.spell_discharge_date,
a.consultant_code,
a.diagnosis_primary_icd,
a.gp AS [GP Code],
a.length_of_stay AS [Spell LOS],
--pat_type,
--prv_typ,
a.spell_special_service AS Spell_Service_ID,
a.date_of_birth,
a.administrative_category,
a.cds_type
--FROM SUS_5NA_NEW_0607 a--
FROM SUS_apc_5NA_view a
WHERE a.fin_month < 4
AND a.fin_year = 2008
AND a.main_episode='M'
AND a.pbr_exclusion IS NULL
AND a.pct_calculated = '5NA'
AND a.commissioner LIKE '5NA%'
AND a.Spell_Discharge_Date<'30/06/2007'

Kristen
Test

22859 Posts

Posted - 2007-10-24 : 06:50:48

Looks like you have different Collation on your two databases/tables.

Where that occurs you will need to "force" the COLLATION of comparisons between columns - stick a
COLLATE xxx
after each such comparison

Kristen

yhassan
Starting Member

8 Posts

Posted - 2007-10-24 : 07:00:48

Thanks for the response Kristen. Apologies, but I am a bit of a newbie and am not sure what you meant. Where exactly in the code would i put the Collate XXX, and what do I put in place of the xxx? Sorry!

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-10-24 : 07:24:17

use collate coallationname agaunst string comparison
like..where Latin1_General_CI_AS being the coalltion name
select * from tablename where column1 = 'dfsdf' collate Latin1_General_CI_AS

yhassan
Starting Member

8 Posts

Posted - 2007-10-24 : 07:29:21

Apologies, but I dont understand. Is it possible to show me where in my code above the code would go? As I understand, I need to put the line Collate Database_default somewhre, but I am not sure where. Is this correct?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-24 : 07:40:31

shallu1_gupta has done that with a simple code

select * from tablename where column1 = 'dfsdf' collate Latin1_General_CI_AS

KH
[spoiler]Time is always against us[/spoiler]

yhassan
Starting Member

8 Posts

Posted - 2007-10-24 : 07:50:06

With respect, as I have already said I didnt understand it the first time, I dont think that simply repeating the same thing will make things any clearer. I have pasted my code in the first post, if anyone could show me what I need to change in this code, this would be most helpful. Thanks.

Kristen
Test

22859 Posts

Posted - 2007-10-24 : 07:59:07

You have posted "miles" of code to be understood and fixed, I doubt anyone has time to do that for you for free, and I also doubt that there is anything like enough information within the query on its own to know which columns come from which tables and thus what actually needs a COLLATE hint.

With due respect: I think that you need to get to understand the fix which is suggested here, and apply it yourself. An example has been provided, and further information is available in Book Online

Kristen

yhassan
Starting Member

8 Posts

Posted - 2007-10-24 : 08:06:52

Perhaps I have confused things by posting too muchg code- only a small section of it is relevent:

CREATE VIEW IP_SLAM_EXTRACT_1
AS
SELECT LEFT(a.provider,3) AS [Trust Code],
WHEN a.practice_code IS NULL AND
a.postcode NOT IN
(SELECT DISTINCT POSTCODE
FROM [Exeter PostCode]) THEN 'Null_not_5NA'
FROM SUS_apc_5NA_view a

Now as I understand it, the problem arises from some issue related to using 2 different table, sus_apc_5na_view and [exeter post code]. But I am not clear on where to put the collate statement, as i dont have a x = y type statement, which the example seems to use. I hope this makes it clearer. To clarify, I am a relatve newbie, and dont understand this collate issue at all, im not even sure what it means, and hence am probably not seeing what people are trying to tell me.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-24 : 11:53:51

Here is a sample of two tables with different collation and how the error can be resolved.

DECLARE @T1 TABLE (String NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI)
DECLARE @T2 TABLE (String NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS)

INSERT @T1
SELECT N'Foo'

INSERT @T2
SELECT N'Foo'

-- Does not work
SELECT *
FROM @T1 AS A
INNER JOIN @T2 AS B
ON A.String = B.String


-- Works
SELECT *
FROM @T1 AS A
INNER JOIN @T2 AS B
ON A.String = B.String COLLATE SQL_Latin1_General_CP1_CI_AI

-- OR

SELECT *
FROM @T1 AS A
INNER JOIN @T2 AS B
ON A.String COLLATE SQL_Latin1_General_CP1_CI_AS = B.String

You should look up COLALTE in books online and try to understand what it is doing. I'd love to help modify your code, but I don;t realy have the information needed to apply a COLLATE statement to your query. We need to know the Collation of each "string" column in each table in order to be able to tell you that (unless you want to apply an abitrary collation).

If you script out the tables in question using Query Analyzer or Management Studio you should be able to get a script that includes the collation for each column for which it is applicable. If you supply us with that then we can help you put the correct COLLATE statement in the correct location.

yhassan
Starting Member

8 Posts

Posted - 2007-10-25 : 04:36:54

Thanks, that cleared things up a lot- i managed to find the name of the collate statement i need (COLLATE Latin1_General_BIN), and the right place to put it (after the select statement). It works fine now- much appreciated!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources