Link to home
Create AccountLog in
Avatar of romeiovasu
romeiovasu

asked on

sql query

i have a query written i am getting the following error.
Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

this is my query.


--select * from oinm

create table #temptable (
itemcode      nvarchar(50),
itemname      nvarchar(255),
whscode            nvarchar(25),
onhand            numeric(19,6),
avgprice      numeric(19,6),
[lastpurchasedate]      smalldatetime,
[Lastsaledate]      smalldatetime)

insert into #temptable(itemcode,itemname,whscode,onhand,avgprice)
select T0.itemcode,itemname,whscode,T0.onhand,T0.avgprice from oitw T0 inner join oitm T1 on T1.itemcode = T0.itemcode where whscode = 'arjay'

select * from #temptable

--select * from opor
--select * from por1
create table #temptable1 (
docdate smalldatetime,
itemcode nvarchar(50))

select max(T0.docdate) as docdate,itemcode into #temptable1 from opor T0 inner join por1 T1 on T0.docentry = T1.docentry group by itemcode


update #temptable set lastpurchasedate = T1.docdate from #temptable T0 inner join #temptable1 T1 on T0.itemcode = T1.itemcode

--select * from oitm
--drop table #temptable1
Avatar of DBAduck - Ben Miller
DBAduck - Ben Miller
Flag of United States of America image

It looks like you have some nvarchar fields that are mapped to different Code Pages.  These are not able to automatically be converted implicitly.  You need to either match the code pages, or you could try in your case to CONVERT the nvarchar() fields to the desired codepages.

http://msdn.microsoft.com/en-us/library/ms187928.aspx

The above article illustrates changing the output collation of a select with a CAST of the data.  It appears that you have a database that has tables that may not have the same collation as the default collation of the database.
So conversion to that collation or creation of the temp table with the matching collation seems to be the quickest way to solve this.
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
The error is bound to be on this statement:

update #temptable set lastpurchasedate = T1.docdate from #temptable T0
inner join #temptable1 T1 on T0.itemcode = T1.itemcode collate SQL_Latin1_General_CP850_CI_AS

Adding the collate clause like acperkins has shown will fix it, but it merely highlights the inconvenience of working with a mismatched server/db collation, as laboriously pointed out by dbaduck.

You also have another error; you cannot create #temptable1, then follow with a select.. into temptable1 (to create it again).
There are at least 3 other ways to fix this issue, besides (1) statement patch with collate clause, (2) properly matching server/db collations (note: tempdb is ALWAYS collated to server default)

#3 - create temp objects with explicit collation on all n/char fields, e.g.
create table #temptable (
itemcode      nvarchar(50) collate database_default,
itemname      nvarchar(255) collate database_default,
whscode            nvarchar(25) collate database_default,
onhand            numeric(19,6),
avgprice      numeric(19,6),
[lastpurchasedate]      smalldatetime,
[Lastsaledate]      smalldatetime)

Open in new window


#4 - create temp objects using select .... into, which preserves origin field collations
select T0.itemcode,itemname,whscode,T0.onhand,T0.avgprice,
[lastpurchasedate]=cast(null as smalldatetime)      ,
[Lastsaledate]=cast(null as smalldatetime)
into #temptable
from oitw T0 inner join oitm T1 on T1.itemcode = T0.itemcode where whscode = 'arjay'

Open in new window


#5 - write a better query not to use temp tables (my choice)
select T0.itemcode,itemname,whscode,T0.onhand,T0.avgprice , lastpurchasedate=T2.docdate
from oitw T0
inner join oitm T1 on T1.itemcode = T0.itemcode
left join (
	select max(T0.docdate) as docdate,itemcode
	from opor T0
	inner join por1 T1 on T0.docentry = T1.docentry
	group by itemcode) T2 on T2.itemcode = T0.itemcode
where whscode = 'arjay'

Open in new window

romeiovasu,

You may want to reconsider your point allocation.  I was merely coroborating dbaduck's comment.  It seems to me the solution presented by dbaduck and cyberkiwi deserved the points.  See here:
I accepted the wrong solution. What do I do?
https://www.experts-exchange.com/help.jsp#hs=29&hi=409