Freaky SQL result

[snerting]

SELECT a.[_ds_dataset] FROM table1 a
WHERE a.instance=595
AND [_ds_dataset] =
(
SELECT MAX(b.[_ds_dataset])
FROM table1 b
WHERE b.[instance] = a.[instance]
AND b.[_ds_dataset] IN (2,1)
)

This SQL returns the following (query-view):
_ds_dataset
1


SELECT a.[_ds_dataset],a.[_ds_delete_object] FROM table1 a
WHERE a.instance=595
AND [_ds_dataset] =
(
SELECT MAX(b.[_ds_dataset])
FROM table1 b
WHERE b.[instance] = a.[instance]
AND b.[_ds_dataset] IN (2,1)
)

Just adding [_ds_delete_object] to be selected gives me this (query-view):
_ds_dataset _ds_delete_object
2 0


So, _ds_dataset has changed in the recordset, even though neither the source tables nor the where clauses have changed!

To make it even freakier:

SELECT a.[_ds_dataset] FROM nc_questions a
WHERE a.instance=595
AND [_ds_dataset] =
(
SELECT MAX(b.[_ds_dataset])
FROM nc_questions b
WHERE b.[instance] = a.[instance]
AND b.[_ds_dataset] IN (2,1)
)
AND [_ds_dataset]=2

Now I hard-coded that _ds_dataset has to be 2. Result (query-view):
_ds_dataset
1


Using Access 2003, Oracle8 ODBC drivers to a Oracle10g database.

This strange behavior does not happen in Oracle when running the Oracle8 sqlplus client directly on the database. Neither does it happen through Python win32com ADODB.

 

[snerting]

Update:

SELECT [_DS_DATASET]
FROM nc_questions
WHERE instance=595;

returns two rows:
1
1


SELECT [_DS_DATASET],[_DS_DELETE_OBJECT]
FROM nc_questions
WHERE instance=595;

returns (of course) two rows:
2
2


I have identified the problem but don't know how to fix it. The primary keys in nc_questions are _ds_dataset and instance. When I link it into Access I don't get them both set as primary, only instance. So, there is confusion in access. I can't explicitly set both as key either, as you cannot change properties of a linked table. The result is then that I get unmanageable results from my queries towards linked tables with more than one column in the primary key.

Has anybody any solution to this problem?

 

[snerting]

Well, finally solved the problem. I dropped and recreated the primary key in oracle and relinked the table in access. The table then had a composite primary key and everything works.

Lesson learned: If results from queries are nonsense and tables are linked - there might be a problem with primary keys.