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
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