Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

the text, ntext and image data can not be compared error

Status
Not open for further replies.

shelby101

Technical User
Apr 21, 2016
17
US
Hi all, can anyone tell me what I am missing here. Using CR2011 and needed to create the following command. Tested piece by piece and command works until I get to the final "TaskNote"."NoteText" I get the error "the text, ntext and image data types cannot be compared or sorted, except when using IS NULL or LIKE - does anyone know how to get around this, because i need that text field in my cammand.

SELECT "TaskNote"."WOID",Count( "TaskNote"."TaskNoteId") AS 'CountNotes', "TaskNote"."TaskNoteTypeId" AS 'TypeId',"TaskNote"."TaskNoteCategoryId" AS 'CatId',"TaskNote"."CreateDate" AS 'Date',"TaskNote"."CreatedBy" AS 'Tech',"TaskNote"."NoteText" AS 'Note'
FROM "TRACKIT_DATA"."dbo"."TaskNote"
WHERE "TaskNote"."TaskNoteTypeId"=2
GROUP BY "TaskNote"."WOID","TaskNote"."TaskNoteTypeId","TaskNote"."TaskNoteCategoryId","TaskNote"."CreateDate","TaskNote"."CreatedBy","TaskNote"."NoteText
 
What is the datasource (Oracle, MS-SQL, Access, etc.)? What are the data types of the fields?
 
Hey Kray4660,

SQL, but I found a workaround for that part.

SELECT "TaskNote"."WOID",Count( "TaskNote"."TaskNoteId") AS 'CountNotes', "TaskNote"."TaskNoteTypeId" AS 'TypeId',"TaskNote"."TaskNoteCategoryId" AS 'CatId',"TaskNote"."CreateDate" AS 'Date',"TaskNote"."CreatedBy" AS 'Tech', CAST ("TaskNote"."NoteText" AS NVARCHAR(MAX)) AS 'Note'
FROM "TRACKIT_DATA"."dbo"."TaskNote"
WHERE "TaskNote"."TaskNoteTypeId"=2
GROUP BY "TaskNote"."WOID","TaskNote"."TaskNoteTypeId","TaskNote"."TaskNoteCategoryId","TaskNote"."CreateDate","TaskNote"."CreatedBy", CAST ("TaskNote"."NoteText" AS NVARCHAR(MAX))

Only problem now is that I am getting a count of 1 on each line, not the actual count of notes for each WOID. ugh
 
So what are you trying to do. It should count taskNoteID grouped by WOID and of course all the other fields. Sometimes it works better to create a subquery to return the count.
 
Yea I tried subquery as well. The goal is to get every workorder that only has 1 note record. (dont ask why this was requested b/c idk :)) Anyway this is the subquery command that i tried and it's still not returning the correct data.

SELECT "TaskNote"."WOID",
"TaskNote"."TaskNoteTypeId" AS 'TypeId',
"TaskNote"."TaskNoteCategoryId" AS 'CatId',
"TaskNote"."CreateDate" AS 'Date',
"TaskNote"."CreatedBy" AS 'Tech',
"TaskNote"."NoteText" AS 'Note'

FROM "TRACKIT_DATA"."dbo"."TaskNote"
WHERE "TaskNote"."WOID" IN

(
SELECT "TaskNote"."WOID"

FROM "TRACKIT_DATA"."dbo"."TaskNote"
WHERE "TaskNote"."TaskNoteTypeId"=2

GROUP BY "TaskNote"."WOID",
"TaskNote"."TaskNoteTypeId",
"TaskNote"."TaskNoteCategoryId",
"TaskNote"."CreateDate",
"TaskNote"."CreatedBy"
HAVING Count( "TaskNote"."TaskNoteId") = 1
)
 
Try:

SELECT "TaskNote"."WOID",
"TaskNote"."TaskNoteTypeId" AS 'TypeId',
"TaskNote"."TaskNoteCategoryId" AS 'CatId',
"TaskNote"."CreateDate" AS 'Date',
"TaskNote"."CreatedBy" AS 'Tech',
"TaskNote"."NoteText" AS 'Note'

FROM "TRACKIT_DATA"."dbo"."TaskNote"
WHERE "TaskNote"."WOID" IN

(
SELECT "TaskNote"."WOID"

FROM "TRACKIT_DATA"."dbo"."TaskNote"
WHERE "TaskNote"."TaskNoteTypeId"=2

GROUP BY "TaskNote"."WOID"
HAVING Count( "TaskNote"."TaskNoteId") = 1
)

Gordon BOCP
Crystalize
 
Hi Gordon, thanks for the suggestion. Still not getting!the WHERE clause for TaskId = 2. This is making me crazy i tell ya!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top