Access SQL vs Oracle

[Guest]

Why would I be getting different results for the same SQL statement when I
run it drirectly in Oracle than what I am getting when I run in MS Access
2002 on the same Oracle table linked through MS Oracle ODBC Driver. The
results run directly in Oracle are correct; the results Access brings back
are not correct. Is the ODBC driver not translating the SQL statement
correctly?

I would appreciate any help.

 

[Larry Linson]

The results run directly in Oracle are
correct; the results Access brings back
are not correct. Is the ODBC driver not
translating the SQL statement
correctly?

That's possible... did you run it as a Passthrough Query?

That tells Jet and ODBC that you created the Query in the server's dialect
of SQL and that it should not be changed in any way, just "pass through" to
the server, unmodified?

Clearly, both Access and Oracle have their own "dialects" of SQL and there's
always the possibility of a "mistranslation". I'll have to say, in years and
years of working on Access clients to server DBs, I've had either none, or
so few instances of mistranslation that I don't remember them. None of my
Access clients, however, used Oracle as the server DB.

Larry Linson
Microsoft Access MVP

 

[Guest]

Thank you Larry. I will try that.

How do I specify that it is a pass-thru Query?

Also, just FYI, in playing with the language, I found out that I have to add
the keyword DISTINT into my select statement in Access to get the same result
I get in Oracle without that keyword..

 

[Douglas J. Steele]

With the query open in Design view, select Query from the menu bar. Pick SQL
Specific from that menu, then Pass-through from the sub-menu. Supply the
connection information and you're done.

 

[Guest]

Hi.

The reason you're getting two different data sets with the same SQL
statement is because you are using Oracle SQL syntax, not ANSI SQL-92 syntax.
The join is in the WHERE clause of your query, not the FROM clause. By
doing this in Jet, your returned data set is a Cartesian Product of the
matching records.

For example, it's valid in Oracle SQL to use the following:

SELECT *
FROM EMPS, DEPT
WHERE EMPS.DEPTNO = DEPT.DEPTNO;

and Oracle will return only the matching records. However, using this same
syntax
in a query in Access will result in a Cartesian Product, whereas:

SELECT *
FROM EMPS INNER JOIN DEPT ON EMPS.DEPTNO = DEPT.DEPTNO;

uses ANSI SQL-92 syntax and Jet will return only the matching records --
which
can be _immensely_ fewer records -- depending upon how many records are in
each table. Oracle will return the same data set of matching records
regardless of which syntax is used (because it was designed to), so you don't
notice a difference in data sets, because there is no difference.

If you want to use Oracle SQL and don't need an updateable recordset, then
use a SQL Pass-through query as others have advised you. But if you use a
linked Oracle table, then Jet will process the query and the ANSI SQL-92
syntax needs to be used.

As you've already discovered, using the DISTINCT keyword with Oracle SQL
syntax in Access will eliminate the extra records and eventually return the
matching records data set, but you'll find out that it's much faster to
process the query when you use the ANSI SQL-92 syntax when there are very
large tables involved. The difference in processing time for large tables is
quite significant.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.