Collation Error
I am trying to run a query for an integration project. The query ran fine on the old version of the database, but we recently upgraded the program to the new Unicode dtabase version. As far as I can tell from the properties windows, both the old datatbase and the new one have the same collation type, but when I run the query, I get Msg 451, Level 16, State 1, Line 2
Cannot resolve collation conflict for column 11 in SELECT statement.
Here is the query. The portion cv
Cannot resolve collation conflict for column 11 in SELECT statement.
Here is the query. The portion cv
Select
hr.res_id
,hr.fullname
,hr.usr_id
,jt.descr50
,hr.comp
,hr.job_title
,hr.loc
,(Select cmp_fadd1 from cicmpy where hr.comp = administration and cmp_type = 'D') as address
,(Select cmp_tel from cicmpy where hr.comp = administration and cmp_type = 'D') as phone
,(Select cmp_fax from cicmpy where hr.comp = administration and cmp_type = 'D') as fax
-- THIS PART IS CAUSING THE PROBLEM
,(Select cmp_fcity + ', ' + statecode + ' ' + cmp_fpc from cicmpy where hr.comp = 'administration' and cmp_type = 'D') as citystate
--------
,hr.ldatindienst
,hr.emp_stat
,hr.costcenter
,a.itemcode
,i.userYesNo_01
,a.enddate
From humres as hr
left join (absences a inner join items i on i.itemcode = a.itemcode and i.userYesNo_01 = 1) on hr.res_id = a.empid and a.type = 86
left join hrjbtl as jt on hr.job_title = jt.job_title
Order by hr.res_id
1. Check the existing collation using the following:
Then change the values based on this info
2. Also, I could not get the meaning on hr.comp = 'administration' in the query. Is it that query is returning multiple rows? Shouldn't there be a joining attribute between hr and cicmpy?
Then change the values based on this info
2. Also, I could not get the meaning on hr.comp = 'administration' in the query. Is it that query is returning multiple rows? Shouldn't there be a joining attribute between hr and cicmpy?
1. SELECT COLLATION_NAME FROM information_schema.columns
where
TABLE_NAME='cicmpy'
2. Select cmp_fcity + ', ' + statecode + ' ' + cmp_fpc from cicmpy where hr.comp = 'administration' and cmp_type = 'D'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For b) I wrote off-topic but now I see the collation-error will (also) be given on the part
hr.comp = 'administration'
And when you fix that by adding the COLLATE it still will be wrong because you don't use the content off the colum cicmpy.adminsrtation but compare with a char-value 'administration'
hr.comp = 'administration'
And when you fix that by adding the COLLATE it still will be wrong because you don't use the content off the colum cicmpy.adminsrtation but compare with a char-value 'administration'
ASKER
It works, although I don't understand why. Why did moving the select statements to only have one join make it work.
"Why did moving the select statements to only have one join make it work."
Technicaly it's not the one join what fixed it, but by the one join you got rid off the difference I mentioned in b) . If your tables are not very tiny you should do a profiler trace of your query (with 4th query fixed as the first 3) and the one with the join. Probably see a large difference .
I have to warn you, in C) I mentioned you'll get an error when more then one row matches your query. Now you get simply an extra result returned.
Technicaly it's not the one join what fixed it, but by the one join you got rid off the difference I mentioned in b) . If your tables are not very tiny you should do a profiler trace of your query (with 4th query fixed as the first 3) and the one with the join. Probably see a large difference .
I have to warn you, in C) I mentioned you'll get an error when more then one row matches your query. Now you get simply an extra result returned.
,(Select cmp_fcity COLLATE DATABASE_DEFAULT + ', ' + statecode COLLATE DATABASE_DEFAULT + ' ' + cmp_fpc COLLATE DATABASE_DEFAULT from cicmpy where hr.comp = 'administration' and cmp_type = 'D') as citystate