CODEWITHSUNDEEP
sqlsql-servert-sqlunion
Alexander
Alexander

Reputation: 20224

The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable

I have a table

which is joined to another table

The Owner can be a Participant, and if it is, the same reference (into user table) is in Owner and Participant. So I did:

SELECT TableA.Id,TableA.Owner,TableA.Text
FROM TableA
WHERE TableA.Owner=@User
UNION
SELECT TableA.Id,TableA.Owner.TableA.Text
FROM TableA LEFT JOIN TableB ON (TableA.Id=TableB.Id)
WHERE TableB.Participant = @User

This query should return all distinct data sets where a certain @User is either Owner or Participant or both.

And it would, if SQL Server wouldn't throw

The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

Since Id is a PK, and Text is from the same table, why would SQL Server want to compare Text at all?

I can use UNION ALL to stop duplicate detection, but can I circumvent this without losing the distinctness of the results?

Upvotes: 29

Views: 49074

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

Correct way

Stop using TEXT it is obsolete. Alter table schema.

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Workaround

Cast to NVARCHAR(MAX):

SELECT TableA.Id,TableA.Owner, CAST(TableA.DescriptionText AS NVARCHAR(MAX))
FROM TableA
WHERE TableA.Owner=@User
UNION
SELECT TableA.Id,TableA.Owner, CAST(TableA.DescriptionText AS NVARCHAR(MAX))
FROM TableA LEFT JOIN TableB ON (TableA.Id=TableB.Id)
WHERE TableB.Participant = @User

Upvotes: 55

Related Questions