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.....

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
|||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|||

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

|||thanks for all you help, it sorted the problem