The error message is correct... "ntext" (and "text") fields cannot be used
the same as varchar (or char) fields... that's part of what makes them
different. ("text" fields can be huge, and are not stored in a way that is
convenient for SQL Server to do the normal kinds of things like grouping and
sorting.)
If you need ntext then you need to change your query. Luckily that's not too
hard...
Here is a method that uses a nested SELECT (doing the GROUP BY) as a table
(aliased to a "table" named "t1") *within* the FROM clause. I can't run
this (obviously) so it may have a syntax error or something, but it ought to
work...
SELECT h3_articles.ARTICLE_ID,
h3_articles.TITLE,
h3_articles.ARTICLE,
h3_articles.CATEGORY_ID,
h3_articles.DATE_POSTED,
h3_articles.POSTED_BY,
h3_articles.TIME_POSTED,
h3_categories.CATEGORY_ID,
h3_categories.CATEGORY,
h3_categories.CATEGORY_ORDER,
t1.NumComments
from
(select h3_articles.ARTICLE_ID,
count(h3_comments.ARTICLE_ID) as NumComments
FROM (h3_articles LEFT OUTER JOIN h3_comments ON (h3_articles.ARTICLE_ID =
h3_comments.ARTICLE_ID))
GROUP BY h3_articles.ARTICLE_ID
) as t1
INNER JOIN h3_articles ON (h3_articles.ARTICLE_ID = t1.ARTICLE_ID)
ORDER BY h3_articles.DATE_POSTED desc, h3_articles.TIME_POSTED desc
-----Original Message-----
From: Howard Owens [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 04, 2002 4:57 PM
To: CF-Talk
Subject: (OT - SQL) Changing Data Type Screws up Query
I want to change the data type on one of my columns. I want to change it
from varchar to ntext.
On my development box, everything works great except one query that has me
stuck.
Here's the error message I'm getting:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image
data types cannot be compared or sorted, except when using IS NULL or LIKE
operator.
But if I try removing the column name from the GROUP BY statement, I get
this error:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'h3_articles.ARTICLE'
is invalid in the select list because it is not contained in either an
aggregate function or the GROUP BY clause.
I just don't know SQL well enough to know where to take it from here.
Here's my Query:
SELECT h3_articles.ARTICLE_ID,
h3_articles.TITLE,
h3_articles.ARTICLE,
h3_articles.CATEGORY_ID,
h3_articles.DATE_POSTED,
h3_articles.POSTED_BY,
h3_articles.TIME_POSTED,
h3_categories.CATEGORY_ID,
h3_categories.CATEGORY,
h3_categories.CATEGORY_ORDER,
count(h3_comments.ARTICLE_ID) as NumComments
FROM (h3_articles INNER JOIN h3_categories ON h3_articles.CATEGORY_ID =
h3_categories.CATEGORY_ID)
LEFT OUTER JOIN h3_comments ON (h3_articles.ARTICLE_ID =
h3_comments.ARTICLE_ID)
GROUP BY h3_articles.ARTICLE_ID,
h3_articles.TITLE,
h3_articles.ARTICLE,
h3_articles.CATEGORY_ID,
h3_articles.DATE_POSTED,
h3_articles.POSTED_BY,
h3_articles.TIME_POSTED,
h3_categories.CATEGORY_ID,
h3_categories.CATEGORY,
h3_categories.CATEGORY_ORDER
ORDER BY h3_articles.DATE_POSTED desc, h3_articles.TIME_POSTED desc
Suggestions, hints, directions???
H.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
