>I have a union clause like this:
>
>
>DECLARE @NoSite Integer
>DECLARE @NoSite2 Integer
>DECLARE @Enabled Bit
>DECLARE @NoSite3 Integer
>DECLARE @Enabled2 Bit
>DECLARE @NoSite4 Integer
>DECLARE @Enabled3 Bit
>DECLARE @NoSite5 Integer
>DECLARE @NoSite6 Integer
>
>SET @NoSite=2
>SET @NoSite2=2
>SET @Enabled=1
>SET @NoSite3=2
>SET @Enabled2=1
>SET @NoSite4=2
>SET @Enabled3=1
>SET @NoSite5=2
>SET @NoSite6=2
>
>SELECT TOP 40 News.Title_E,News.Title_F,News.Url,News.AddDate AS Date,News.Url2,1 AS Type,
>News.NoSite,'' AS Title2
>FROM News WHERE News.NoSite=@NoSite
>UNION
>SELECT TOP 40 Event.Title AS Title_E,Event.Title AS Title_F,Event.Url,Event.AddDate AS Date,
>convert(char(74),'') AS Url2,2 AS Type,Event.NoSite,'' AS Title2
>FROM Event
>LEFT JOIN Province ON Event.NoProvince=Province.Numero
>WHERE Event.NoSite=@NoSite2 AND Event.Enabled=@Enabled
>
>
>I obtain "The text data type cannot be selected as DISTINCT because it is not comparable.". This is in regards to the syntax where the Url2 field does not exist in the Event table. What would be the proper syntax to have a union with the Event table considering that News.Url2 is char(74)?
Which field is Text datatype? If you're using SQL 2005 and up, cast TEXT field to varchar(max).
Also, IMHO, it doesn't make much sense to have char(74) field. I would use varchar(74) instead.
Finally, if you want to get rid of the error, use UNION ALL instead of UNION.
If it's not broken, fix it until it is.
My Blog