Re: Access97 views only certain Oracle indexes

>>A query that contains a sub-query all based on a single attached
 table,
>>and listed below, only seems to work properly when using tables that
 are
>>in Access or SQL-Server. When attached to the exact same table in
>>Oracle, I do not receive any records back from my query.
>>
>>Since I don't have a SQL-Server database, a Microsoft tech tested my
>>problem, with the correct results. Oracle techs were unwilling to
 test
>>the problem.

>I am an Oracle employee. Here I am....

Thanks for the reply.

>I think this might be a problem with your ODBC driver or your local
>system.

Sorry, the version I supplied was for the ODBC administrator. I was actually using version 8.00.0400 from Oracle. I've since downloaded their latest version (8.0.4.4.0), relinked all the tables, but was still unable to get the correct query results.

>>1. from a new Access97 database create a table called TEST with the
>>following
>>structure:
>>
>>Field Name: "ID" Data Type: "Long Integer" Index: "No Duplicates"
>>Field Name: "NAME" Data Type: "Text"
>>
>>2. populate about 3 records with junk data.
 

>>3. export the table to Oracle as "TEST_ORACLE" and also to the
>>Northwinds database (or any other Access97 database) as "TEST_ACCESS".
   

>>4. link the remote tables to your database.

>I successfully did steps 1 through 4.

>>5. create the following sub-query and save it as "x2"
>>
>>SELECT TEST.NAME
>>FROM TEST
>>WHERE (((TEST.NAME)="99"));
>>
>>Note: "99" should not be present in any of the populated records.

>I did this step, although, since it is not used below, I counldn't
>figure out why you wanted this performed. Maybe it was just to
>perfectly recreate the steps of your scenario.

This query is the sub-query that is necessary in order to reproduce the problem. It should be saved as "x2" and is used in both SQL statements ("z1" and "z2") below.

>>6. create the following queries. save the first one as "z1" and the
>>second as "z2":
>>
>>SELECT [TEST_ACCESS].Name
>>FROM [TEST_ACCESS] LEFT JOIN x2 ON [TEST_ACCESS].Name = x2.NAME;
 

>I saved this in the default Northwinds database.

>>SELECT TEST_ORACLE.NAME
>>FROM TEST_ORACLE LEFT JOIN x2 ON TEST_ORACLE.NAME = x2.NAME;
>>---------
>
>By default, Access prepends the Oracle schema owner to the table name,
>so for my queries, replace "TEST_ORACLE" with "SCOTT_TEST_ORACLE".
>

That's ok. The problem is that the Oracle based query is not displaying all of contents of the field (TEST_ORACLE.NAME) on the left side of the join. It seems as if it is just ignoring the "LEFT JOIN" clause.

>>That's it.
>>
>>If you run the "z1" query, you should see the NAME records that you
>>entered - this is correct.
>
>This query, against the external data from the Northwinds database,
>was correctly returned.

>>If you run the "z2" query, you should see nothing returned - this is
>>obviously NOT correct.
>
>This query, against the external data from the Oracle database, was
>also correctly returned. This IS correct.
>
>I did this using MS Access 97 and Oracle8 8.0.4 on Solaris. The ODBC
>driver is 8.0.4.3.0 (developed by Oracle). You can grab the
>Oracle-developed ODBC driver for free from our web site (at
>http://www.oracle.com) to try and isolate the problem a little
>further.

>Joel R. Kallman

Thanks again.

Troy Perchotte
Digital Courier International Received on Tue Jun 30 1998 - 00:00:00 CDT