asked on

Collation Conflit

I have run into this problem multiple times now and cannot figure out why (I think it may be SQL related). I have two data sources used by ColdFusion. Each connects to a different database, but the table structure of the two databases is exactly the same. When I added the line of code used to generate the field "overrideqty" I get an error message that says "[Macromedia][SQLServer JDBC Driver][SQLServer]Cannot resolve collation conflict for equal to operation." However, when adding the same line to the other data source, it works just fine. I have attached the SQL code below. Any help would be appreciated. Thanks.

      SELECT
            ioh.[Location Code] AS LOCNCODE,
            loc.[Name] AS LOCNDESC,
            i.No_ AS ITEMNMBR,
            i.[Description 2] AS ITEMDESC,
            i.[Item Category Code] AS CATEGORY,
            cast(0 as int) as uskusF,
            cast(0 as int) as uskusQ,
            cast(0 as int) as uwip,
            cast(0 as int) as gskus,
            cast(0 as int) as gskusq,
            cast(0 as int) as gskust,
            kskus = case when ioh.[Location Code] = '001' then cast(SUM(ioh.[Remaining Quantity]) as int) else cast(0 as int) end,
            kskusq = case when ioh.[Location Code] = '002' then cast(sum(ioh.[Remaining Quantity]) as int) else cast(0 as int) end,
            cast(0 as numeric(18,2)) as zerovalue,
            (select top 1 qty from supersonics.vitals.dbo.opsinvdemandoverrides o where location = 'GmbH' and o.itemnmbr = i.No_ order by overrideid desc) as overrideqty
      FROM LMV.dbo.[LeMaitre Vascular K_K_$Item Ledger Entry] ioh INNER JOIN LMV.dbo.[LeMaitre Vascular K_K_$Item] i ON ioh.[Item No_] = i.No_ inner join LMV.dbo.[LeMaitre Vascular K_K_$Location] loc on ioh.[Location Code] = loc.Code
      WHERE ioh.[Location Code] in ('001', '002')
      group by
            ioh.[Location Code],
            loc.[Name],
            i.No_,
            i.[Description 2],
            i.[Item Category Code]

pinaldave

SELECT
ioh.[Location Code] AS LOCNCODE,
loc.[Name] AS LOCNDESC,
i.No_ AS ITEMNMBR,
i.[Description 2] AS ITEMDESC,
i.[Item Category Code] AS CATEGORY,
cast(0 as int) as uskusF,
cast(0 as int) as uskusQ,
cast(0 as int) as uwip,
cast(0 as int) as gskus,
cast(0 as int) as gskusq,
cast(0 as int) as gskust,
kskus = case when ioh.[Location Code] collate database_default = '001' then cast(SUM(ioh.[Remaining Quantity]) as int) else cast(0 as int) end,
kskusq = case when ioh.[Location Code] database_default = '002' then cast(sum(ioh.[Remaining Quantity]) as int) else cast(0 as int) end,
cast(0 as numeric(18,2)) as zerovalue,
(select top 1 qty from supersonics.vitals.dbo.opsinvdemandoverrides o where location = 'GmbH' and o.itemnmbr = i.No_ order by overrideid desc) as overrideqty
FROM LMV.dbo.[LeMaitre Vascular K_K_$Item Ledger Entry] ioh INNER JOIN LMV.dbo.[LeMaitre Vascular K_K_$Item] i ON ioh.[Item No_] database_default = i.No_ database_default inner join LMV.dbo.[LeMaitre Vascular K_K_$Location] loc on ioh.[Location Code] database_default = loc.Code database_default
WHERE ioh.[Location Code] in ('001', '002')
group by
ioh.[Location Code],
loc.[Name],
i.No_,
i.[Description 2],
i.[Item Category Code]

mnmorency

ASKER

Got the following error message when using your updated code:

[Macromedia][SQLServer JDBC Driver][SQLServer]Line 14: Incorrect syntax near 'database_default'.

ASKER CERTIFIED SOLUTION

RCorfman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mnmorency

ASKER

The reference https://www.experts-exchange.com/questions/21048175/Cannot-resolve-collation-conflict-for-equal-to-operation.html from RCorfman did the trick. I had to just put the line "collate database_default" after any variable referenced in the where statement of the line used to define the alias. Thanks.

RCorfman

That's good to hear. You may want to research the other links a little and look into the coolation definition in your environments to see if you can mitigate the need to do this for future queries. I have the feeling it has something to do with a temporary table being created because of the query requirements... and that using a different default collation. But, good to hear you are over the bump.