PRB: SELECT DISTINCT Fails with Microsoft ODBC for Oracle (277667)

The information in this article applies to:

Microsoft ODBC for Oracle version 2.5 Build 2.573.2927
Microsoft ODBC for Oracle version 2.5 Build 2.573.3513
Microsoft ODBC for Oracle version 2.5 Build 2.573.3711
Microsoft ODBC for Oracle version 2.5 Build 2.573.4202
Microsoft ODBC for Oracle version 2.5 Build 2.573.4403
Microsoft ODBC for Oracle version 2.5 Build 2.573.5303
Microsoft ODBC for Oracle version 2.5 Build 2.573.6019
Microsoft ODBC for Oracle version 2.5 Build 2.573.6526
Microsoft ODBC for Oracle version 2.5 Build 2.573.7326
Microsoft ODBC for Oracle version 2.5 Build 2.573.7626
Microsoft ODBC for Oracle version 2.5 Build 2.573.7713.2
Microsoft Data Access Components 2.1
Microsoft Data Access Components 2.5
Microsoft Data Access Components 2.6
Microsoft Data Access Components 2.7

This article was previously published under Q277667

SYMPTOMS

When a SELECT DISTINCT statement is used with the Microsoft ODBC for Oracle driver, the following error may occur:

DIAG [HYC00] [Microsoft][ODBC driver for Oracle]Cannot use Keyset-driven cursor on join, with distinct clause, union, intersect or minus or on read only result set (0)

CAUSE

The Microsoft ODBC for Oracle driver uses the hidden Oracle column ROWID, which assigns a unique ID for each row. Because this negates the uniqueness constraint imposed by the DISTINCT keyword, the driver issues the above error rather than bring back multiple copies of the same data.

RESOLUTION

If you require the use of the DISTINCT keyword, you can use the Microsoft OLE DB Provider for Oracle, because the provider doesn't use the ROWID column when DISTINCT is used. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

258997 INFO: Oracle OLE DB Provider and ROWID Use

There is no workaround for this behavior with the Microsoft ODBC for Oracle driver.

MORE INFORMATION

Steps to Reproduce Behavior

Create a table to test with:
```
CREATE TABLE FOO (C1 CHAR(20))
						
```

Insert some data into the table:

INSERT INTO FOO VALUES('aaa')
INSERT INTO FOO VALUES('bbb')

Using the ODBC API SQLSetConnectAttr, set the connection attribute SQL_CURSOR_TYPE to the value SQL_CURSOR_KEYSET_DRIVEN. This ensures that you get a keyset cursor, which uses the hidden ROWID column.
Execute the following SQL statement:
```
SELECT DISTINCT * FROM FOO
						
```

The ODBC call returns SQL_ERROR, and the associated error text will be the error shown in the "Symptoms" section of this article.

Modification Type:	Major	Last Reviewed:	12/5/2003
Keywords:	kbOracle kbprb KB277667