April 27, 2008 at 11:41 pm
Hello everyone, i m using SQLServer2005 .
select count(TvsRecordID),FormImage
from TvsRecords
group by FormImage
Here is the query when i run this, this give me following Message: "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."
Kindly tell me what can i do? as i must select the FormImage field , as my query also has some fields where i applied the Sum or count expressions, in that case how i pass FormImage
field in group by clause?
Thanx in Advance. Plz reply me asap its very urgent.
April 27, 2008 at 11:50 pm
Hi
Image data type cant be used in a Group BY clause.
Why do you need to group by a image column, cant you use any other column?
"Keep Trying"
April 28, 2008 at 12:09 am
I don't want to group by on Image field, i just want this in my select list, but as i told in my post, some fields have expression so if i not used this on my group by clause, it give me following msg:
Msg 8120, Level 16, State 1, Line 2
Column 'TvsRecords.FormImage' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Plz tell me how can i get this field in my select stmt, without using it in the Group by stmt.
April 28, 2008 at 12:31 am
You cannot use a fieldname with a aggregate function in select statement without mentioning it in the group by clause. As pointed out by chirag you cannot use an image field in the group by clause.
Why you require to display an image field with an aggregate query?
April 28, 2008 at 5:24 am
Hi
Is TvsRecordID a primary key or a identity column.
Can you group by any other column.
Pls post the table structure and sample data and let us see.
"Keep Trying"
April 28, 2008 at 1:04 pm
What you can do, in some cases, is aggregate on a different column, in a derived table/CTE, then join to that using the image column.
More likely, what you need to do, is find out why you'd have the same image data in more than one row of the table. Is that the case? If so, can you move that to a different table?
For the first idea, it goes something like this:
;with Agg (ID, Qty) as
(select ID, count(*)
from dbo.Table
group by ID)
select Table.ID, Qty, ImageColumn
from dbo.Table
inner join Agg
on Table.ID = Agg.ID
That will give you the count for that ID (or use another row to aggregate on), and allow you to include the image in the final query.
Will that do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2008 at 12:01 am
That looks like a nice suggestion. you can aggregate on another column and then join it with your table to get the image column. But you need another column to aggregate on.
You can also use a derived table for this .
select Table.ID, D.Qty, Table.ImageColumn
From (select ID, count(*)
from dbo.Table
group by ID) D INNER JOIN Table
on D.ID = Table.ID
But the first thing is why do u want to select a image column?
"Keep Trying"
May 14, 2008 at 10:56 pm
There can be reasons for selecting an image column. For example in my case. I am querying MOSS content DB and the document is stored as an image type.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply