DISTINCT QUESTION

[makisbest]

Dar Friends

Why when I put the DISTINCT statement in my SQL query the VB6 return me an error.
In my SQL Query Analyzer just work fine.

strsql = "SELECT DISTINCT * FROM DOCHDSAL, LITMSALE”

It is Service Pack Problem ?

 

[HarleyQuinn]

What error does VB return?

Harleyquinn

---------------------------------
For tsunami relief donations

 

[makisbest]

Run-time error '-2147217900 (80040e14)':

The text,ntext, or image data type cannot be selected as distinct

 

[HarleyQuinn]

And you say when you run it in SQLServer it returns the values as expeted??

Harleyquinn

---------------------------------
For tsunami relief donations

 

[makisbest]

yes

 

[HarleyQuinn]

Just out of interest what values are you expecting to return form your query??

Harleyquinn

---------------------------------
For tsunami relief donations

 

[makisbest]

3 fields of integer and about 666 records. Without distinct returns about 1300 double records.

3,12,12
4,5,12
6,2,9

 

[HarleyQuinn]

Why not try a SELECT * and then group by all of the fields at the end???

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

And include a join in the query between your two tables.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[johnwm]

SQL Books on Line says <When querying, Microsoft® SQL Server™ does not allow the use of SELECT DISTINCT on a text, ntext, or image column.>

I guess that one of the fields included in your * collection falls into that category. You can get Books On Line as a download from:

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Everyone who uses MSSQL or MSDE should have a copy to hand!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[tsdragon]

I don't think you can do DISTINCT on multiple fields like that. Try DISTINCT fieldname. If you must use multiple fields concatenate them and then use DISTINCT on that.


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[HarleyQuinn]

I know in Oracle you can do a DISTINCT * but it doesn't return the values you would be wanting to return from a distinct query...

Harleyquinn

---------------------------------
For tsunami relief donations

 

[GerardMcL]

Hi,

Not sure if this will work but I tried it and worked for me.

SELECT DISTINCT(ID) As disID, Name, Amount FROM <table>

It would return DISTINCT IDS and the names and amounts that match it. Dont know if this will help