Collation conflict in SQL Union All Query

Question

There is a Union All query as shown below. When fired in SQL server, I get an error

"Cannot resolve collation conflict for column 1 in SELECT statement."

Please, where do I add the Collate database_default statement with this Union All query?

select  OrgCode,null as OrgName,
Q1_A1 as A1Value,Q1_a2 as A2Value,'Quarter 1' as dt,((Q1_A1-Q1_A2)/case when Q1_A1<>0 then Q1_A1 else null end )*100 Percentage
from #Tabl_Quarter_Calculation

union all

select  OrgCode,null as OrgName,Q2_A1 as A1Value,Q2_a2 as A2Value,'Quarter 2' as dt,((Q2_A1-Q2_A2)/case when Q2_A1=0 then null else Q2_A1 end  )*100 Percentage
from #Tabl_Quarter_Calculation

union all

select [OrgCode],[OrgName],a1Value,a2Value,dt
,cast((a1value-a2value)/cast(a1value as real)*100 as varchar(10))+'%' Percentage
 from #Tbl_Display1

union all

--4 week average
select [OrgCode],[OrgName],sum(a1Value) as a1Value,Sum(a2Value) as a2Value,max(dt) as dt
,cast((sum(a1value)-sum(a2value))/cast(sum(a1value) as real)*100 as varchar(10))+'%' Percentage
 from #Tbl_Display1
group by   [OrgCode],[OrgName]
order by 1,5

Lawry

which version of SQL server are you using? 2000,2005 or 2008? – Deciliter

Answer 1

Collation needs to be specified wherever strings from different collations are compared, commonly after a JOIN or WHERE condition. With a UNION, the columns in each of the the SELECT statements are compared to ensure they can be unioned. Your error tells you it is column 1, so that is where you must specify the collation. Try the below

select  OrgCode COLLATE database_default,null as OrgName,
Q1_A1 as A1Value,Q1_a2 as A2Value,'Quarter 1' as dt,((Q1_A1-Q1_A2)/case when Q1_A1<>0 then Q1_A1 else null end )*100 Percentage
from #Tabl_Quarter_Calculation

union all

select  OrgCode COLLATE database_default,null as OrgName,Q2_A1 as A1Value,Q2_a2 as A2Value,'Quarter 2' as dt,((Q2_A1-Q2_A2)/case when Q2_A1=0 then null else Q2_A1 end  )*100 Percentage
from #Tabl_Quarter_Calculation

union all

select [OrgCode] COLLATE database_default,[OrgName],a1Value,a2Value,dt
,cast((a1value-a2value)/cast(a1value as real)*100 as varchar(10))+'%' Percentage
 from #Tbl_Display1

union all

---4 week average
select [OrgCode] COLLATE database_default,[OrgName],sum(a1Value) as a1Value,Sum(a2Value) as a2Value,max(dt) as dt
,cast((sum(a1value)-sum(a2value))/cast(sum(a1value) as real)*100 as varchar(10))+'%' Percentage
 from #Tbl_Display1
group by   [OrgCode],[OrgName]
order by 1,5

Deciliter