Do not understand this message help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Do not understand this message help

Author

Topic

csphard
Posting Yak Master

113 Posts

Posted - 2005-06-05 : 18:07:11

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

csphard
Posting Yak Master

113 Posts

Posted - 2005-06-05 : 18:09:22

This is the sql giving the problem and nothing is wrong with it.

csphard
Posting Yak Master

113 Posts

Posted - 2005-06-05 : 18:10:33

This is the sql giving the problem and nothing is wrong with it.

select count(srv_maritalStatus) as mycount,srv_maritalStatus from college_survey where srv_college = Academy of Chinese Culture and Health Sciences
GROUP BY srv_maritalStatus

I took out single quotes around Academy of Chinese Culture and Health Sciences

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-05 : 18:18:41

If srv_college is a text or ntext column, you cannot use the equality operator (=) on it, which is what the error message is telling you.

csphard
Posting Yak Master

113 Posts

Posted - 2005-06-05 : 18:29:52

How do i get my where then. I will have different colleges.

csphard
Posting Yak Master

113 Posts

Posted - 2005-06-05 : 18:32:15

And the statement does work at first

CREATE TABLE [college_survey] (
[srv_id] [int] IDENTITY (1, 1) NOT NULL ,
[srv_college] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[srv_currentChildcare] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[srv_campusChildcare] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[srv_barriers] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[srv_barriersOther] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[srv_maritalStatus] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[srv_ageGroup] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[srv_gender] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[srv_ethnicity] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[srv_ethnicityOther] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[srv_highestEd] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[srv_monthlyGross] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[srv_careNeededwhen] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[srv_careNeededwhenOther] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[srv_currentLivingArrangements] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[srv_currentLivingArrangementsOther] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[srv_numChildren] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[srv_numChildren02] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[srv_numChildren25] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[srv_numChildren56] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[srv_numChildren6] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[srv_homeComputer] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[srv_inputDate] [datetime] NOT NULL CONSTRAINT [DF_college_survey_srv_inputDate] DEFAULT (getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-05 : 18:53:22

For one thing, you do not need to use text for any of the columns you're using it for. There's no way you need more than a 10-20 characters to describe gender, marital status, age group or ethnicity, and it's unlikely any of the other columns need more than 50-100 characters. Change all of the text columns to varchar and you'll eliminate the problem.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-06-05 : 19:59:09

And what's the error?
Probably due to srv_maritalStatus being a blob but the error message should tell you that.

Fromt your other thread

SELECT srv_maritalStatus = convert(varchar(100),srv_maritalStatus), Count(*) as mycount, CAST(Count(*) as float) * CAST( 100 as float) / Cast( ( SELECT Count(*) FROM college_survey where srv_college = 'Academy of Chinese Culture and Health Sciences ') as float) as percentage FROM college_survey where srv_college = 'Academy of Chinese Culture and Health Sciences 'GROUP BY convert(varchar(100),srv_maritalStatus)

should work.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources