How can I fix this?

If I delete line 2, query work exactly.

SELECT 
     case when ID_DonVi_Di > 0 then 'dv' cast(ID_DonVi_Di as nvarchar) else MaChiNhanh_Di end as ID_NoiDi
    , case when ID_DonVi_Di > 0 then dvdi.Ten_DonViNgoai else cndi.TenChiNhanh end as NoiDi 
    , case when ID_DonVi_Den > 0 then 'dv' cast(ID_DonVi_Den as nvarchar) else MaChiNhanh_Den end as ID_NoiDen
    --, case when ID_DonVi_Den > 0 then dvden.Ten_DonViNgoai else cnden.TenChiNhanh end as NoiDen
FROM 
    [dbo].[ToTrinh] a
left join
    dbo.DM_CHINHANH cndi on a.MaChiNhanh_Di = cndi.MaChiNhanh COLLATE Latin1_General_CI_AS
left join
    dbo.DM_CHINHANH cnden on a.MaChiNhanh_Den = cnden.MaChiNhanh COLLATE Latin1_General_CI_AS
left join
    [dbo].DM_DonViNgoai dvdi on a.ID_DonVi_Di = dvdi.ID_DonViNgoai
left join
    [dbo].DM_DonViNgoai dvden on a.ID_DonVi_Den = dvden.ID_DonViNgoai
where a.TonTai = 1

enter image description here

CodePudding user response：

You need to change the collation of one field to match with the collation of the other field.

For example, if column A has collation Latin1_General_CI_AS...

and column B has collation SQL_Latin1_General_CP1_CI_AS...

You could do something like this:

CASE WHEN X > 0 THEN A ELSE B COLLATE Latin1_General_CI_AS END

Or something like like this:

CASE WHEN X > 0 THEN A COLLATE SQL_Latin1_General_CP1_CI_AS ELSE B END