Grouping by ntext Field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Grouping by ntext Field

Author

Topic

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-04-24 : 15:14:01

Hello,

I have this query (in short and for example

):

---------------------------------------------------------------
SELECT TABLEO.ACCTNUM, T11.USERMEMO ....

FROM TABLE0 LEFT OUTER JOIN

(SELECT TABLE1.USERMEMO, TABLE1.ACCTNUM
FROM TABLE1 WHERE ... ) AS T11 ON TABLE0.ACCTNUM = T11.ACCTNUM

....
....

GROUP BY TABLE0.ACCTNUM, T11.USERMEMO
-------------------------------------------------------------

Where table1.usermemo is an ntext field.

I am experiencing this error:

The text, ntext and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. (Microsoft SQL Server, Error: 306)

I believe the error is due to sorting on the usermemo field because it is ntext. Is there any other way to accomplish joining and grouping this field with the rest of my data?

Thank you in advance.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-24 : 15:32:03

GROUP BY TABLE0.ACCTNUM, cast(T11.USERMEMO as nvarchar(4000))

If the memo value is longer than 4000 characters you'll lose them in the grouping expression. Of course if you actually need to group on something like that...

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-04-24 : 15:37:24

quote:
Originally posted by robvolk

GROUP BY TABLE0.ACCTNUM, cast(T11.USERMEMO as nvarchar(4000))

If the memo value is longer than 4000 characters you'll lose them in the grouping expression. Of course if you actually need to group on something like that...

THANK YOU!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources