CODEWITHSUNDEEP
sqlsql-server
bobo2000
bobo2000

Reputation: 1877

SQL Union failing

SELECT TOP 5 
  Notices.Id, 
  NoticeLoc.Notices_Id,
  Loc.Id as Loc_Id, 
  CAST(Notices.Text AS TEXT) as Text, 
  CAST(Notices.Title AS TEXT) as Title, 
  Notices.CDate as RDate 
FROM NoticeLoc 
  JOIN Notices ON NoticeLoc.Notices_Id=Notices.Id JOIN Loc ON NLoc.Loc_Id=Loc.Id WHERE Loc_Id IN (1)

UNION 

SELECT TOP 5 
  Notices.Id, 
  '',
  '', 
  CAST(Notices.Text AS TEXT) as Text, 
  CAST(Notices.Title AS TEXT) as Title, 
  Notices.CDate as RDate 
FROM NoticeC 
  JOIN Notices ON NoticeC.Notices_Id=Notices.Id WHERE C_Id=110 AND CDate BETWEEN '10/01/2011' AND '07/14/2025' ORDER BY RDate desc

I am trying to perform a union between 2 result sets using mssql, however I am getting the following error:

Msg 421, Level 16, State 1, Line 1 The text data type cannot be selected as DISTINCT because it is not comparable.

I have to keep those fields as text fields.

How can I resolve this, cheers.

Upvotes: 1

Views: 2044

Answers (2)

Miika L.
Miika L.

Reputation: 3353

The problem is the type of the data (TEXT). Are you able to change the table to use VARCHAR(MAX) instead? Or drop the TEXT fields from the union? Or perhaps just cast the fields to VARCHAR(MAX) in your query?

VARCHAR(MAX) is functionally the same as text (unlimited length, not stored in the row, etc.).

Upvotes: 0

Steve
Steve

Reputation: 216243

UNION by itself is interpreted as using DISTINCT (probably because UNION is trying to mesh the two queries into one DISTINCT list inherently). Use UNION ALL.

Upvotes: 5

Related Questions