Outer Join Problem via ODBC to Oracle 8.1.5

We are doing outer joins in sql run via Oracle ODBC driver 8.01.05.0.0 to Oracle 8.1.5.

If we run:

select name2.given_name, title2.dsc from
{oj prsn_name name2 left outer join name_title title2 on
name2.name_title_id = title2.Name_title_id } where name2.given_name like 'B%'
union
select name2.given_name, title2.dsc from
{oj prsn_name name2 left outer join name_title title2 on
name2.name_title_id = title2.Name_title_id } where name2.given_name like 'S%'

We get an error saying syntax error or access violation.

If we run the first part of the select it works fine or the second part of the select it works fine but when we have a union of two statements with outer joins we get this error. Further if we re-code our outer joins with different syntax as

select name2.given_name, title2.dsc from prsn_name name2 ,name_title title2
where name2.given_name like 'S%' and
name2.name_title_id (+) = title2.name_title_id union
select name2.given_name, title2.dsc from prsn_name name2 ,name_title title2
where name2.given_name like 'B%' and
name2.name_title_id (+) = title2.name_title_id

we do not get an error. The problem is that our syntax must work against both an Oracle and SQL Server database so although the second syntax would work in Oracle it would not run in SQL Server.

Any ideas or suggestions?

Thanks,
P.Merks

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 03 1999 - 15:18:03 CST