Re: Porting OUTER JOIN statements to Oracle 8i

Hi,

bill_at_wirespring.com (Bill Gerba) wrote in message news:<516329be.0108081211.162432f0_at_posting.google.com>...
> Hello,
>
> I have some fairly complex queries that were developed in mySQL that I
> now need to port over to an Oracle 8i database. The queries join many
> tables together, almost always with outer joins. I've run into a
> problem, though, where I can't use OR in my outer joins in Oracle. I
> need to be able to do something like this:
>
> SELECT t1.*, t2.name, t2.id
> FROM company AS t1 LEFT JOIN salesrep AS t2
> ON (t2.id = t1.rep1 OR t2.id = t1.rep2)
>

How about an union ?
 SELECT t1.*, t2.name, t2.id
 FROM company AS t1 LEFT JOIN salesrep AS t2  where t2.id(+) = t1.rep2
 union
 SELECT t1.*, t2.name, t2.id
 FROM company AS t1 ,salesrep AS t2
 where t2.id(+) = t1.rep1

>
> Thanks in advance,
>
> Bill Gerba

Hth
 Thomas Received on Thu Aug 30 2001 - 04:16:40 CDT