[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

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

Open in new window

0
red_75116
Asked:
red_75116
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
COLLATE DATABASE_DEFAULT  

,(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
0
 
sameer2010Commented:
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?
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'

Open in new window

0
 
jogosCommented:
a) collation
For conversion see sollution  - aneeshattingal
How it went wrong: your database now with a new default collation, tables (or data) created with old version can still have a different collation.   depends on what has happened

OFF TOPIC
b) Odd in your query
 3 times                         where hr.comp = administration  --    is there a column administration in table cicmpy ?
and 4th thime written :  where hr.comp = 'administration'  --  means char-value 'administration'

c) inline select
ex ,(Select cmp_fadd1 from cicmpy where hr.comp = administration and cmp_type = 'D') as address
this only works if no row or just one result matches that select otherwise ..... error 'multiple rows... where only on row expected'

d) repeated same query
for the inline selects for address, fax ..  whynot including it in the join? so you only call that table once
Select 
hr.res_id 
,hr.fullname  
,hr.usr_id 
,jt.descr50 
,hr.comp 
,hr.job_title 
,hr.loc 
, cicmpy.cmp_fadd1 as address
, cicmpy.cmp_tel as phone
, cicmpy.cmp_fax as fax
, cicmpy.cmp_fcity COLLATE DATABASE_DEFAULT+ ', ' + cicmpy.statecode COLLATE DATABASE_DEFAULT+ ' ' + cicmpy.cmp_fpc COLLATE DATABASE_DEFAULT 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 
--
left join cicmpy ON hr.comp = cicmpy.administration and cicmpy.cmp_type = 'D'
--
Order by hr.res_id

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jogosCommented:
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'
0
 
red_75116Author Commented:
It works, although I don't understand why.  Why did moving the select statements to only have one join make it work.
0
 
jogosCommented:
"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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now