DISTINCT w/ character data

Hello,
I need to eliminate duplicates from records containing a text data type.
Here is the query I try :
select NewsGroup.NewsGroupID,
(distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
-- Line 10
NewsGroup.OnlineFlag
from...
where...
--
And here is the error I get :
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'distinct'.
--
The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what I
am trying to do is possible. But then why this error? If this is not
possible, how else could I eliminate the duplicates?
TIADISTINCT applies to the whole result not just one column. Maybe this
will do what you intended (notice the extra bracket and comma):
SELECT newsgroup.newsgroupid,
MAX(CAST(A.translatedtext AS VARCHAR(8000))) AS newsgroupname,
newsgroup.onlineflag
FROM a
WHERE ...
GROUP BY newsgroup.newsgroupid, newsgroup.onlineflag ;
David Portas
SQL Server MVP
--|||The keyword DISTINCT needs to be before any field names. Also, CAST should b
e
outside of the parentheses. Try the following
SELECT DISTINCT NewsGroup.NewsGroupID, CAST (a.TranslatedText as
varchar(8000)) as NewsGroupName ....
"alto" wrote:

> Hello,
> I need to eliminate duplicates from records containing a text data type.
> Here is the query I try :
> --
> select NewsGroup.NewsGroupID,
> (distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
> -- Line 10
> NewsGroup.OnlineFlag
> from...
> where...
> --
> And here is the error I get :
> --
> Server: Msg 156, Level 15, State 1, Line 10
> Incorrect syntax near the keyword 'distinct'.
> --
> The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what
I
> am trying to do is possible. But then why this error? If this is not
> possible, how else could I eliminate the duplicates?
> TIA
>
>sql

Displaying Chinese Character in varchar columns

I would like to store some data in Chinese Big5 using a varchar column. However, all the data turn in to some garbage characters like Ru￥Uao? .
I ran into no problem if i use nvarchar but I would like to use varchar because I can only store less than 4000 characters using nvarchar.

It works if I paste the characters directly into the SQL Express Client. It only turns into some garbage characters if i use C++ to insert the data.

I am using Chinese_Taiwan_Stroke_BIN collation (The chinese won't show if I use the default latin collation) on the column and my computer regional setting for non-unicode application is Traditional Chinese(Taiwan).

Any ideas how can I fix this problem? I am suspecting there's a problem with my computer setting?

Thanks!

If you need more than 4000 characters, you should consider using an NText column type. A standard varchar column can only hold characters from its native character set.|||but then ntext insertion and retrival time would be slower and we don't want that

Displaying Character Format in Numeric Field

I have a Calculated Member that displays values of 999.0 when the calculation cannot be computed based on certain criteria. I would like to be able to format the numeric field so that is displays a character such as "X" when the value of the Calculated Member is 999.0. Does anyone know how to do this?

If this is in Analysis Services 2005, you should be able to use the IIF() MDX function to return either the value or "x".|||

Thanks. That worked!

Displaying Character Format in Numeric Field

I have a Calculated Member that displays values of 999.0 when the calculation cannot be computed based on certain criteria. I would like to be able to format the numeric field so that is displays a character such as "X" when the value of the Calculated Member is 999.0. Does anyone know how to do this?

If this is in Analysis Services 2005, you should be able to use the IIF() MDX function to return either the value or "x".|||

Thanks. That worked!