Oracle + ODBC Outer Joins + Drivers

From: Charles Gamble <Charles.Gamble_at_Singularity.co.uk>
Date: Wed, 13 Sep 2000 11:39:25 +0100
Message-ID: <ATIv5.17857$8r4.176965@news2-win.server.ntlworld.com>

Hi all,

I am having a problem when using the ODBC {oj} Outer Join escape sequence with Oracle ODBC drivers. When I use the current Oracle ODBC driver from Oracle everything works fine. However, when I use the current Microsoft Oracle ODBC driver I get problems when doing outer joins with more than one table. (I am doing the JOINs using primary key fields).

The following works on both the Microsoft and Oracle ODBC drivers (I am not using the real table and column names here): SELECT TABLE1.COL1, TABLE2.COL1, TABLE3.COL1 FROM
{oj TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.ID1 = TABLE2.ID1 AND
TABLE1.ID2 = TABLE2.ID2 AND TABLE1.ID3 = TABLE2.ID3} The following works when using the Oracle ODBC driver but doesn't work using the Microsoft driver:
SELECT TABLE1.COL1, TABLE2.COL1, TABLE3.COL1 FROM
{oj TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.ID1 = TABLE2.ID1 AND
TABLE1.ID2 = TABLE2.ID2 AND TABLE1.ID3 = TABLE2.ID3 LEFT OUTER JOIN TABLE3 ON TABLE1.ID1 = TABLE3.ID1} I known Oracle supports these kinds of JOINs. Any ideas why this is not working when using the Microsoft driver? I am using MDAC 2.5 Service Pack 1, so I should have the most current driver.

Thanks,

Charles Gamble. Received on Wed Sep 13 2000 - 05:39:25 CDT