Collation Conflict - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Collation Conflict

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-04-16 : 05:10:56

Guys,

When running my script i just encounter this kind of error.
need your helop guys to find out whatis wrong with my script.
even i place a "COLLATE DATABASE_DEFAULT" on the 9 colum of group by still i got an error.
Thank you in advance.

Msg 451, Level 16, State 1, Line 4
Cannot resolve collation conflict for column 9 in GROUP BY statement.

Here is my script..


Select
      rma.ESN,
      PurchID,
      POReceiptDate,
      ReturnDate,
      ShipDate,
      PurchPrice,
      ReturnSKU,
      Case when VENDACCOUNT in ('tlk','tpp') then sup.SupplierCode else VENDACCOUNT end as Supplier,
      MODEL,
      FailCode,
      REASONCODE,
      SHIPRETURNTYPE,
      SalesID,
      MAX(ReceiptDate) as MostRecentReceipt
into #RMA
from ##RMABenefit rma
left outer join dbo.AsiaESNReceipts rec
      on rma.esn = rec.ESN COLLATE Chinese_Taiwan_Stroke_CI_AS
      and rma.returndate > rec.ReceiptDate
left outer join dbo.ESNSupplier sup
      on rec.SupplierID = sup.SupplierID 

group by
      rma.ESN,
      PurchID,
      POReceiptDate,
      ReturnDate,
      ShipDate,
      PurchPrice,
      ReturnSKU,
      Case when VENDACCOUNT in ('tlk','tpp') then sup.SupplierCode else VENDACCOUNT end,
      MODEL COLLATE DATABASE_DEFAULT,
      FailCode,
      REASONCODE,
      SHIPRETURNTYPE,
      SalesID

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-04-16 : 05:21:54

I got it..it's working!

Just place a collation in the when condition after the "sup.SupplierCode"


Case when VENDACCOUNT in ('tlcshk','tcp') then sup.SupplierCode COLLATE Chinese_Taiwan_Stroke_CI_AS else VENDACCOUNT end as Supplier,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-16 : 12:11:14

for case when columns returned through all branches should be compatible ie in data type,collation etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-04-16 : 20:46:58

Thanks Visakh16 for the input.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources