-1

I am using data in my query from another database from same server

I am getting the given error

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP850_BIN2" in the equal to operation.. Native error: 468. SQLSTATE: 42000. Severity 16. MsgState 9. Line 39.

How do I fix it?

with
 main AS (
SELECT a.LBrCode,a.PrdAcctId,b.SexCode,a.DateOpen,
case when (a.AcctStat=3 and a.DateClosed>'30-jun-2021') or a.AcctStat<>3 then 'live' else 'cl' end r1
FROM ac_mast a INNER JOIN prd_mat b ON a.CustNo=b.CustNo
WHERE rtrim(substring(a.PrdAcctId,1,8)) IN ('abc') AND a.AcctType=32

)
,
card_data AS (
SELECT substring(b.primary_a_c_number,1,3) LBrCode,
substring(b.primary_a_c_number,4,4)+replicate(' ',4)+replicate('0',8)+substring(b.primary_a_c_number,8,8)+replicate('0',8) PrdAcctId,
card_number,card_process_date,first_tran_date
FROM [hk_RDS2.0].dbo.cardx1 b

select a.LBrCode,a.PrdacctId,b.Card_number from main a inner join card_data b
on a.LBrCode=b.LBrCode and a.PrdAcctId=b.PrdAcctId

please help

Dale K
  • 25,246
  • 15
  • 42
  • 71
hemu_k
  • 1
  • 5
  • 2
    Does this answer your question? [Issues with missing collate in SQL Server](https://stackoverflow.com/questions/69001222/issues-with-missing-collate-in-sql-server) – allmhuran Sep 04 '21 at 12:16
  • I tried as SELECT a.LBrCode,a.PrdAcctId,b.card_number FROM main a INNER JOIN card_data b ON a.LBrCode COLLATE DATABASE_DEFAULT = b.LBrCode COLLATE DATABASE_DEFAULT AND a.PrdAcctId COLLATE DATABASE_DEFAULT =b.PrdAcctId COLLATE DATABASE_DEFAULT but getting error as Expression type int is invalid for COLLATE clause.. Native error: 447. SQLSTATE: 42000. Severity 16. MsgState 1. Line 39. – hemu_k Sep 04 '21 at 12:24
  • 1
    [Edit] your question, @hemu_k , don't put that information in the comments. – Thom A Sep 04 '21 at 12:35
  • 1
    The error, however, is telling you the problem. You clearly have 2 different collations here and this the values can't be compared without explicitly collating one to the other's. – Thom A Sep 04 '21 at 12:43
  • help me Larnu ...how do compare? – hemu_k Sep 04 '21 at 12:52
  • @hemu_k as per the duplicate posted in the first comment. – Dale K Sep 04 '21 at 23:24
  • You can only apply collations to character columns, so your attempt to fix it with the inclusion of `AND a.PrdAcctId COLLATE DATABASE_DEFAULT =b.PrdAcctId COLLATE DATABASE_DEFAULT` doesn't make sense, assuming these "id" columns are ints. Just apply the collation to the code column: `on a.lbrcode = b.lbrcode collate database_default` – allmhuran Sep 05 '21 at 09:24

1 Answers1

0

Just to take this one off the board...

Change the last part of your query to this:

select      a.LBrCode, 
            a.PrdacctId, 
            b.Card_number 
from        main      a 
inner join  card_data b on a.LBrCode = b.LBrCode collate database_default
                           and a.PrdAcctId = b.PrdAcctId
allmhuran
  • 4,154
  • 1
  • 8
  • 27