Hi

I am trying to use the ODBC driver to do a select distinct query on a column in a table using CF and there is an error:

---
Error Diagnostic Information
ODBC Error Code = S0000 ()


[Inmagic, Inc.][DB/Text ODBC Driver]Unable to SELECT DISTINCT this column: url_address
---

The same query without the DISTINCT works fine.

Any ideas?

David

You can't SELECT DISTINCT on a LONGTEXT field. You can specify that it should be treated as a TEXT file in the textbase INI file.

If you haven't run DBSTATS (which creates the appropriate section in the textbase INI file based on the data in an export file) for this textbase, I'd recommend doing so.

I have come across this on a number of occasions. It seems as though the driver only enables a Select DISTINCT query to only return on one column. e.g.

Select DISTINCT LASTNAME
from TheTable

This will work, but:

Select DISTINCT LASTNAME, ID
from TheTable

Will not.

Cheers
Duncan

quote:

---

Originally posted by dnw:
Hi

I am trying to use the ODBC driver to do a select distinct query on a column in a table using CF and there is an error:

---
Error Diagnostic Information
ODBC Error Code = S0000 ()


[Inmagic, Inc.][DB/Text ODBC Driver]Unable to SELECT DISTINCT this column: url_address
---

The same query without the DISTINCT works fine.

Any ideas?

David

---

[This message has been edited by dedwards (edited 21 November 2002).]

You can SELECT DISTINCT up to five fields, if I recall correctly. If you need to SELECT more than five fields using DISTINCT, you're out of luck I'm afraid.

I've also found that adding a mask validation to the field in Edit Textbase Structure can make the difference between being able to SELECT DISTINCT a field and getting the error message - a mask of just * will make DISTINCT work while still allowing anything in the field. I believe this works because the driver regards an input mask as putting a limit on the length of the field, thereby converting it from LONGTEXT to TEXT.