Post reply

cannot resolve collation conflict for equal to operation

  • May 30, 2012 at 2:07 pm

    #269647

    I've tried adding COLLATE latin1_general_cs_ai but I still receive the error. It worked fine when I did a normal query, but when I created a view with the same statement I'm getting that error. If it's something simple I'm not seeing it. Any help would be greatly appreciated.

    SELECT l.Id, CRM.dbo.fn_ConvertSFDate(e.CreatedDate) AS lastevaluationdate__c, e.ProductId__c AS lastevaluationproduct__c

    FROM Customers.dbo.Opportunity AS l INNER JOIN

    (SELECT MAX(Id) AS id, OpportunityId__c

    FROM Customers.dbo.Evaluation__c AS e

    GROUP BY OpportunityId__c) AS td ON l.Id = td.OpportunityId__c LEFT OUTER JOIN

    Customers.dbo.Evaluation__c AS e ON td.id = e.Id

    WHERE (l.IsDeleted = 0) AND (COALESCE (l.Last_Evaluation_Product__c, '') = '' OR

    COALESCE (l.Last_Evaluation_Date__c, '') = '') AND (l.IsDeleted = 0) AND (COALESCE (e.ProductId__c, '') <> '')

  • May 30, 2012 at 2:17 pm

    #1494885

    i'm guessing that the cross database call to a date formatting function is the culprit. the otehr database has a different collation.

    try this (collating the function value) as the first three lines of your query:

    SELECT

    l.Id,

    CRM.dbo.fn_ConvertSFDate(e.CreatedDate) COLLATE SQL_Latin1_General_CP1_CI_AS AS lastevaluationdate__c,

    and here's the full statement i think i would try, formatted a little different:

    --COLLATE SQL_Latin1_General_CP1_CI_AS

    SELECT

    l.Id,

    CRM.dbo.fn_ConvertSFDate(e.CreatedDate) COLLATE SQL_Latin1_General_CP1_CI_AS AS lastevaluationdate__c,

    e.ProductId__c AS lastevaluationproduct__c

    FROM Customers.dbo.Opportunity AS l

    INNER JOIN (SELECT

    MAX(Id) AS id,

    OpportunityId__c

    FROM Customers.dbo.Evaluation__c AS e

    GROUP BY OpportunityId__c

    ) AS td

    ON l.Id = td.OpportunityId__c

    LEFT OUTER JOIN Customers.dbo.Evaluation__c AS e

    ON td.id = e.Id

    WHERE (l.IsDeleted = 0)

    AND (COALESCE (l.Last_Evaluation_Product__c, '') COLLATE SQL_Latin1_General_CP1_CI_AS = ''

    OR

    COALESCE (l.Last_Evaluation_Date__c, '') COLLATE SQL_Latin1_General_CP1_CI_AS = '')

    AND (l.IsDeleted = 0)

    AND (COALESCE (e.ProductId__c, '') COLLATE SQL_Latin1_General_CP1_CI_AS <> '')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 30, 2012 at 2:18 pm

    #1494886

    Kind of hard to help when we don't have the DDL for the tables 🙂 Also, default collation, database collation, view definition...

    Jared
    CE - Microsoft

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply