471,084 Members | 1,046 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,084 software developers and data experts.

Oracle ODBC Outer Join Bug?

Both the Microsoft and the Oracle ODBC driver will take an outer join
with the "{oj....}" ODBC syntax and Put the (+) on one column as
determined by order of the columns in the "Left Outer Join" portion of
the query
What the heck do I mean by that?

For instance:
select foo.col1 foo, nvl(bla.col1, 'NULL') bla
from {oj foo left outer join bla on FOO.col1 = BLA.col1};

Is equal to:
select foo.col1 foo, nvl(bla.col1, 'NULL') bla
from FOO, BLA
where FOO.col1 = BLA.col1(+);

Notice that FOO and BLA are in the same order in the LOJ and the ON
portion of the queries
While this:
select foo.col1 foo, nvl(bla.col1, 'NULL') bla
from {oj FOO left outer join BLA on BLA.col1 = FOO.col1};

Is equal to this:
select foo.col1 foo, nvl(bla.col1, 'NULL') bla
from FOO, BLA
where bla.col1 = foo.col1(+);

Now the (+) is on the wrong side!
It seems to be dictated by the LOJ portion of the statement
Isn't the ODBC driver or the RDBMS smart enough to figure out which
column belongs to which table?

This wasn't the case with the Microsoft driver on Oracle 8

Anyone else encounter this?
I need this SQL to work on SQL Server as well
The only option i see is to not use the "{oj" because it gets the (+)
right in that case
There must be a fix or patch for those that need truly portable ODBC

Id appreciate a reall solution if you have any ideas...thanks
Jul 19 '05 #1
0 3085

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by deepak.rao | last post: by
3 posts views Thread by Jon Ole Hedne | last post: by
2 posts views Thread by egoldthwait | last post: by
11 posts views Thread by lenygold via DBMonster.com | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.