The text, ntext, and image data types cannot be compared or sorted, ex

[mikeyd]

Hi, I am doing this and its giving me this error ?????

*******************************************************
Server: Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Server: Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.


*****************************************************

SELECT CONTACT1.CONTACT AS Contact, CONTACT1.COMPANY AS Company, CONTACT1.ADDRESS1 AS Address1,
CONTACT1.ADDRESS2 AS Address2, CONTACT1.ADDRESS3 AS Address3, CONTACT1.CITY AS City,
CONTACT1.STATE AS State, CONTACT1.ZIP AS Zip, CONTACT1.COUNTRY AS Country, CONTACT1.TITLE AS Job_Title,
CONTACT1.PHONE1 AS Direct_Line, CONTACT1.KEY3 AS OSA, CONTACT2.UCONTCTROL AS Contacts_Role,
cast(CONTSUPP.notes as varchar) As Notes,
min(contsupp.rectype) as Rectype, min(CONTSUPP.CONTSUPREF) AS Email

FROM CONTACT1 INNER JOIN CONTACT2 ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO
LEFT OUTER JOIN CONTSUPP ON CONTACT2.ACCOUNTNO = CONTSUPP.ACCOUNTNO
WHERE CONTACT1.CONTACT like '*main%'

GROUP BY CONTACT1.CONTACT, CONTACT1.COMPANY, CONTACT1.ADDRESS1, CONTACT1.ADDRESS2, CONTACT1.ADDRESS3,
CONTACT1.CITY, CONTACT1.STATE, CONTACT1.ZIP, CONTACT1.COUNTRY, CONTACT1.TITLE,
CONTACT1.PHONE1, CONTACT1.KEY3, CONTACT2.UCONTCTROL, CONTSUPP.notes

 

[SQLBill]

What happens if you change the last part of the GROUP BY from this: CONTSUPP.notes to just Notes?

GROUP BY CONTACT1.CONTACT, CONTACT1.COMPANY, CONTACT1.ADDRESS1, CONTACT1.ADDRESS2, CONTACT1.ADDRESS3,
CONTACT1.CITY, CONTACT1.STATE, CONTACT1.ZIP, CONTACT1.COUNTRY, CONTACT1.TITLE,
CONTACT1.PHONE1, CONTACT1.KEY3, CONTACT2.UCONTCTROL, Notes


-SQLBill

 

[SQLBill]

Also, please do not post the same question twice. (duplicate post at Thread183-667651).

-SQLBill

 

[HerbAndEdnaWeinstein]

I'm having a very similar problem. I tried removing the fully qualified name from the GROUP BY clause, and it just gave me an "unable to parse" error.

I also tried CONVERT()ing the problematic TEXT column to a VARCHAR so that it could be sorted. The trouble is that this takes a loooonnnnggg time. After 23 minutes, the query hadn't even returned 10 records!

Am I doing something wrong? Help me SQLBill, you're my only hope

hw

 

[HerbAndEdnaWeinstein]

FWIW, here's my code:



SELECT TOP 100

dbo.vProperty.PropertyStatusID AS SQLStatusID,

MAX(dbo.vProperty.vPropertyHeaderID) AS SQLHeader,

dbo.xOrionPropIDAcadPropID.PropertyID AS [ACAD ID],

dbo.xOrionPropIDAcadPropID.AcadPropertyID AS [Orion ID],

CAST(dbo.vPropertyLegal.LegalDescription AS varchar(16)) AS Expr1

FROM dbo.vProperty
INNER JOIN dbo.vPropertyLegal ON
dbo.vProperty.vPropertyHeaderID = dbo.vPropertyLegal.vPropertyHeaderID
CROSS JOIN dbo.xOrionPropIDAcadPropID
GROUP BY dbo.vProperty.PropertyStatusID, dbo.xOrionPropIDAcadPropID.PropertyID, dbo.xOrionPropIDAcadPropID.AcadPropertyID,
CAST(dbo.vPropertyLegal.LegalDescription AS varchar(16))
HAVING (dbo.vProperty.PropertyStatusID = 38500)

 

[HerbAndEdnaWeinstein]

Never-mind on my problem. I got it solved by using two separate queries:

The first query has all the GROUPing logic in it.

The second query inner joins the troublesome text field to the first query via an INNER JOIN to a serial number that is in both queries.

hw

 

[SQLBill]

Sometimes SQL Server expects the same name in the GROUP BY as used in the SELECT. When you alias a column (column_name AS Column_Alias), you should use the alias whenever allowed.

-SQLBill