1

I'm trying to select a view & inner join it with another table in SQL Server 2012 but it is showing this error

Msg 468, Level 16, State 9, Line 18
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.

This is my query:

SELECT 
    vpr.PARTNUMBER, vpr.DESCRIPTION, 
    vpr.BUYER, vpr.[GL CLASS], vpr.[ABC CODE], 
    vpr.TODAY, vpr.DAY1, vpr.DAY2, vpr.DAY3, vpr.DAY4, vpr.DAY5, 
    vpr.[COO QTY], 
    ROUND(vpr.[QOH-MSTORE], 0) AS [QOH-MSTORE], 
    vpr.[QOH-COO], vpr.[QOH-SDV], 
    ABS(ROUND(vpr.TOPUPQTY, 0)) AS [TOPUPQTY],
    (CASE 
        WHEN ABS(ROUND(vpr.TOPUPQTY, 0)) < vpr.[QOH-SDV] THEN ABS(ROUND(vpr.TOPUPQTY, 0))
        WHEN ABS(ROUND(vpr.TOPUPQTY, 0)) >= vpr.[QOH-SDV] THEN vpr.[QOH-SDV]
     END) AS [SDV TO PICK]
FROM 
    vw_PartsReport vpr
LEFT JOIN
    (SELECT twudc.Description1 
     FROM tbl_WH_UserDefineCodes twudc
     WHERE twudc.UserDefineCodes = 'SO'
     GROUP BY twudc.Description1) AS [SOI] ON vpr.PARTNUMBER = Description1
WHERE 
    vpr.TOPUPQTY <= 0

Can someone please advise me what's the problem with my query? Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alvan
  • 39
  • 1
  • 11
  • You are trying to compare strings of two different collation types. You need to explicitly state the collation to use in the equals operation, e.g. `SomeColumn = SomeOtherColumn COLLATE SQL_Latin1_General_CP1_CI_AS`. If you double click on the error message in SSMS it should take you to the offending line. – GarethD Nov 26 '14 at 08:51
  • Yes GarethD, when i double click the error it brings me to "AS [SOI] ON vpr.PARTNUMBER = Description1". For "vpr.PARTNUMBER" is (nvarchar, not null) and "Description1" is (nvarchar, null), so base on this how can i work around it? – Alvan Nov 26 '14 at 09:03

1 Answers1

3

So it's either 

on vpr.PARTNUMBER collate Latin1_General_CI_AI = Description1

or

on vpr.PARTNUMBER  = Description1 collate Latin1_General_CI_AI

depending on which one is which collation. try one and if it's not right try the other, the required information isn't in your post to tell you which one it is.

Tristan
  • 1,004
  • 7
  • 14