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
SQL.
Id appreciate a reall solution if you have any ideas...thanks