join problem

Hi All

I am tring to get some data from the customer table and linking it to a view

select ca.account_status, cs.code from customer as ca
inner join v_customer_status as cs on ca.account_status = cs.description

The reference between the tables is the account_status and the description

The error I am getting back is:

Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

Not sure what i means.....

jbothwel
check the collation of the two fields account_status and description
even if they are from the same type they have to be from the same collation
Teo
The columns are NOT sharing the same collation, therefore they cannot be joined easily. What you an d is, to force them (or one of them) to use the same (normally one of the already existing one of the columns) collation like te other by using the COLLATE keyword:

select ca.account_status, cs.code from customer as ca
inner join v_customer_status as cs on ca.account_status = cs.description COLLATE SQL_Latin1_General_CP1_CI_AI

(change the collation and the column for your special enviroment)

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---
John Calcote
thanks for all you help, it sorted the problem
Bhavin Vyas
The columns in your JOIN might not be of the same COLLATION. You can work around this problem by adding a COLLATE statement on after you column names to explicitly set the collation...

select ca.account_status, cs.code from customer as ca
inner join v_customer_status as cs on ca.account_status COLLATE SQL_Latin1_General_CP1_CI_AS = cs.description COLLATE SQL_Latin1_General_CP1_CI_AS
join problem