Hello,
I've been going around this but I haven't found a solution for my problem. My sql query is:
SELECT dbo.Country.CtyRecID, dbo.Country.CtyShort, dbo.Notification.NotRecID,
dbo.Notification.NotName, dbo.TemporalSuspension.TCtsCode, dbo.TemporalSuspension.TCtsCodeRecID,
dbo.TaxPhylum.PhyName AS Taxon, dbo.TemporalSuspension.TCtsNotes, dbo.TemporalSuspension.TCtsRecID,
dbo.TemporalSuspension.TCtsKgmRecID, CASE dbo.TemporalSuspension.TCtsKgmRecID WHEN 1 THEN 'Animals'
WHEN 2 THEN 'Plants' ELSE 'All' END AS Kingdom
FROM dbo.TemporalSuspension INNER JOIN
dbo.Notification ON dbo.TemporalSuspension.TCtsStartNotRecID = dbo.Notification.NotRecID INNER JOIN
dbo.Country ON dbo.TemporalSuspension.TCtsCtyRecID = dbo.Country.CtyRecID INNER JOIN
dbo.TaxPhylum ON dbo.TemporalSuspension.TCtsCodeRecID = dbo.TaxPhylum.PhyRecID AND dbo.TemporalSuspension.TCtsCode LIKE 'PHY'
UNION ALL
SELECT dbo.Country.CtyRecID, dbo.Country.CtyShort, dbo.Notification.NotRecID,
dbo.Notification.NotName, dbo.TemporalSuspension.TCtsCode, dbo.TemporalSuspension.TCtsCodeRecID,
dbo.TaxClass.ClaName AS Taxon, dbo.TemporalSuspension.TCtsNotes, dbo.TemporalSuspension.TCtsRecID,
dbo.TemporalSuspension.TCtsKgmRecID, CASE dbo.TemporalSuspension.TCtsKgmRecID WHEN 1 THEN 'Animals'
WHEN 2 THEN 'Plants' ELSE 'All' END AS Kingdom
FROM dbo.TemporalSuspension INNER JOIN
dbo.Notification ON dbo.TemporalSuspension.TCtsStartNotRecID = dbo.Notification.NotRecID
INNER JOIN
dbo.Country ON dbo.TemporalSuspension.TCtsCtyRecID = dbo.Country.CtyRecID INNER JOIN
dbo.TaxClass ON dbo.TemporalSuspension.TCtsCodeRecID = dbo.TaxClass.ClaRecID AND dbo.TemporalSuspension.TCtsCode LIKE 'CLA'
But I don't understand why it doesn't work, I keep getting this error :
Cannot resolve collation conflict for column 7 in SELECT statement.
What's wrong? I've used this other times and I never got this problem.
thanks