Hello,
I am trying to get data from a table that looks like this:
Database = Data Table = PersonalData
IDNO FName LName Expiration
12345 John Doe 10/24/2006
23547 John Doe 10/25/2007


There are about 4000 records. The IDNO is unique for each, but many of
the FName and LNames are the same. It is a long story, but there are
many, many repeat names for different people in our database. Due to
the way our database was set up (don't ask) a normal SELECT will pull
the same row many times. Till now, I have been copying the data to an
Excel spreadsheet and using Filter, but I would like to do this in SQL
if possible.

I wrote a query that basically looks like this:
Select Distinct IDNO, FName, LName, Expiration

I get the error message "The text, ntext, or image data type cannot be
selected as DISTINCT." Is there any other way to pull the record only
once?

I hope this is enough data. If not I will supply more.
I am a long time IT guy, but only using SQL for the past month or so.
Also, due to the nature of our database I cannot create tables or copy
to new columns.

Thanks for any help,
Rodjk #613

Hi

Posting DDL would help and may have shown you what was wrong
http://www.aspfaq.com/etiquette.asp?id=5006. At a guess FName and/or LName
is defined as being text or ntext, which is overkill for what they will
hold.

Try:

SELECT DISTINCT IDNO, CAST(FName AS varchar(50)) AS FName, CAST(LName AS
varchar(50)) AS LName, Expiration
FROM Data
ORDER BY IDNO

John

<rjkardo (AT) gmail (DOT) com> wrote

John Bell wrote:
Hello John,
Sorry about the DDL. I will have to try that next time.
But your solution worked! Thanks!
I have messed with that for 2 days.

If you have time, could you explain what you did?
I thought there was an issue with the IDNO, why does adjusting the
names make it work?

Thanks again!
If you are ever in Kabul, I owe you...uh, a coke...as we cannot get
beer.


Rodjk #613

<SNIP my original Post>

Hi

The error message tells you almost everything. "The text, ntext, or image
data type cannot be
selected as DISTINCT." Distinct applies to all columns not just the first,
and the first one looking like a number and the last one looking like a
date, it was probably one or both of the other two columns are text or ntext
data types. Using the CAST function will convert the datatype of the
expression (column) to the one you specified i.e. from text to varchar.

It would be better if you address the choice of datatypes in your table
definition rather than in the query. Check out the ALTER TABLE command in
books online.

John

<rjkardo (AT) gmail (DOT) com> wrote

John Bell wrote:
It is only converting it in the query field, though? Is that right?
I cannot change the structure of the tables in any way.
We have a front end that is very fragile...

I will look that up. The problem is that I cannot alter any of the
table structures in this database. So anything I pull up to change or
alter can only be shown in the query field.

Thanks again,
this has really been a big help
Rodjk #613

John Bell wrote:
Ok, I pulled a DDL. This is what it looks like:

CREATE TABLE [PersonalData] (
[GUID] [uniqueidentifier] NOT NULL CONSTRAINT
[DF__PersonalDa__GUID__7CC477D0] DEFAULT (newid()),
[ENROLL_DATE] [datetime] NULL ,
[Title] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LNAME_SOUNDEX] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[FNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FNAME_SOUNDEX] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MNAME_SOUNDEX] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,

There is LOTS more, so I did not put the whole thing. But these should
(I hope) show enough about the FNAME and LNAME fields.

It seems that character type is NVARCHAR.

I found this:
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp
I am reading it, but am not sure if I understand it yet.

It is late at night here, I will check again in the morning.
I just want to make sure I did not change anything...
Sorry for being such a newb...

Thanks again,
Rodjk #613
Thanks again,
Rodjk #613

Hi

There are no text, ntext or image columns in the DDL posted, but it does not
show IDNO or Expiration.

Which version of SQL Server (SELECT @@VERSION) are you using?

John


<rjkardo (AT) gmail (DOT) com> wrote

John Bell wrote:
Hello,
Ok, it says "Microsoft SQL Server 2000 - 8.00.760

I did not include the whole thing, but all of the other fields are
either NVARCHAR or
DateTime.
Rodjk #613

Hi

That makes the error message confusing!

What happens if you force NVARCHAR?

SELECT DISTINCT IDNO, CAST(FName AS nvarchar(50)) AS FName, CAST(LName AS
nvarchar(50)) AS LName, Expiration
FROM Data
ORDER BY IDNO

John

<rjkardo (AT) gmail (DOT) com> wrote

John Bell wrote:
Hello,
Thanks for all of your help, I really do appreciate it...

If I force NVARCHAR it works, as it did forcing it to VARCHAR.
There are still a few things I need to figure out about the DISTINCT
pull, as sometimes I still get more than one IDNO when I do a pull. I
think that is because there is a conflict in another part of my query.

Just so nothing I do changes the structure of this database.
I can query however much I want, but I can do nothing to have an affect
on the actual structure.

Sorry if this is confusing. I do appreciate the input.

Rodjk #613