TEXT columns in SQL Server are considered Large Object data and therefore aren't indexable/searchable. They're also deprecated. So, actually, the problem is in your database, not in your query.
If you change the column type to a varchar(max), you can store the same amount of character data but shouldn't have this problem.
You should list the columns you need in your SELECT query and you can change the column which is text data type in your query with a CAST function to varchar(max)(or nvarchar(max) if your column is ntext).
Here is an example:
SELECT DISTINCT col1, CAST(textCol as varchar(max)) as textCol, col3 FROM yourtable
Never use SELECT * with UNION. Always specify the columns, even if it is all of them, and make sure that the order and number of the columns is exactly the same on both sides of the UNION.
No, I was right. Using UNION ALL does not produce the error. I created the following test. With UNION I got the same error as you, but with UNION ALL, I get no error. Replace UNION with UNION ALL and your error will go away if you are doing it correctly:
CREATE TABLE dbo.TextTest (
[ID] int NOT NULL
, TextData text NULL
, CONSTRAINT PK_TT
PRIMARY KEY ([ID])
);
INSERT INTO dbo.TextTest ([ID], TextData) VALUES (1, 'hello');
INSERT INTO dbo.TextTest ([ID], TextData) VALUES (2, 'this is a test');
SELECT 'Top' AS Foo, * FROM dbo.TextTest WHERE [ID]=1
UNION ALL
SELECT 'Bottom' AS Foo, * FROM dbo.TextTest WHERE [ID]=2
DROP TABLE dbo.TextTest;
-Tab Alleman
Marked as answer by Amacado on Feb 29, 2012 06:11 AM
SELECT 'FromTable1' AS 'Text1',
col1,
Cast(textCol AS VARCHAR(max)) AS textCol,
col3
FROM yourtable1
UNION
SELECT 'FromTable2' AS 'Text1',
col1,
Cast(textCol AS VARCHAR(max)) AS textCol,
col3
FROM yourtable2
Amacado
Member
22 Points
13 Posts
The text data type can not be selected as DISTINCT because it is not comparable.
Feb 28, 2012 01:47 PM|LINK
Hey there,
i got the following Error Message:
The text data type can not be selected as DISTINCT because it is not comparable.
I know my SQL Query is not correct but i do not know what is wrong!
Is isn't it allowed to combine as the following example:
If I delete "table.*" it works fine :-/ but i need all data From "table".
Can anyone help me?
Justin Dwyer
Participant
1119 Points
196 Posts
Re: The text data type can not be selected as DISTINCT because it is not comparable.
Feb 28, 2012 01:52 PM|LINK
TEXT columns in SQL Server are considered Large Object data and therefore aren't indexable/searchable. They're also deprecated. So, actually, the problem is in your database, not in your query.
If you change the column type to a varchar(max), you can store the same amount of character data but shouldn't have this problem.
limno
All-Star
117340 Points
8005 Posts
Moderator
MVP
Re: The text data type can not be selected as DISTINCT because it is not comparable.
Feb 28, 2012 02:06 PM|LINK
You should list the columns you need in your SELECT query and you can change the column which is text data type in your query with a CAST function to varchar(max)(or nvarchar(max) if your column is ntext).
Here is an example:
SELECT DISTINCT col1, CAST(textCol as varchar(max)) as textCol, col3 FROM yourtable
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
TabAlleman
All-Star
15571 Points
2700 Posts
Re: The text data type can not be selected as DISTINCT because it is not comparable.
Feb 28, 2012 03:01 PM|LINK
Never use SELECT * with UNION. Always specify the columns, even if it is all of them, and make sure that the order and number of the columns is exactly the same on both sides of the UNION.
Amacado
Member
22 Points
13 Posts
Re: The text data type can not be selected as DISTINCT because it is not comparable.
Feb 28, 2012 03:27 PM|LINK
Is there a posibility to use * in UNION function?
I need it cause i have multiple Tables with different colums but i want them in one query (with table as parameter)
TabAlleman
All-Star
15571 Points
2700 Posts
Re: The text data type can not be selected as DISTINCT because it is not comparable.
Feb 28, 2012 04:44 PM|LINK
It is possible, as long as both sides of the UNION are from the same table. It just isn't a good idea, for reasons like the error you are getting.
However, if you can use UNION ALL instead of UNION, I think it will work because it won't need to do a DISTINCT query behind the scenes.
Amacado
Member
22 Points
13 Posts
Re: The text data type can not be selected as DISTINCT because it is not comparable.
Feb 28, 2012 04:55 PM|LINK
i use the same tables on every side and getting the error I get.
TabAlleman
All-Star
15571 Points
2700 Posts
Re: The text data type can not be selected as DISTINCT because it is not comparable.
Feb 28, 2012 05:05 PM|LINK
No, I was right. Using UNION ALL does not produce the error. I created the following test. With UNION I got the same error as you, but with UNION ALL, I get no error. Replace UNION with UNION ALL and your error will go away if you are doing it correctly:
limno
All-Star
117340 Points
8005 Posts
Moderator
MVP
Re: The text data type can not be selected as DISTINCT because it is not comparable.
Feb 28, 2012 05:16 PM|LINK
Try this:
SELECT 'FromTable1' AS 'Text1', col1, Cast(textCol AS VARCHAR(max)) AS textCol, col3 FROM yourtable1 UNION SELECT 'FromTable2' AS 'Text1', col1, Cast(textCol AS VARCHAR(max)) AS textCol, col3 FROM yourtable2Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm