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

[shelby101]

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

 

[kray4660]

What is the datasource (Oracle, MS-SQL, Access, etc.)? What are the data types of the fields?

 

[shelby101]

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

 

[kray4660]

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.

 

[shelby101]

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
)

 

[CrystalizeCanada]

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

 

[shelby101]

Hi Gordon, thanks for the suggestion. Still not getting!the WHERE clause for TaskId = 2. This is making me crazy i tell ya!