On 8 Aug 2001, bill_at_wirespring.com wrote:
> 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)
>
> I tried to simulate this in oracle with a subquery, but I can't
> seem to get it to "refer" to the 'outer loop' so to speak. I
> would like to do something like this:
>
> SELECT t1.* from company t1,
> (select t2.name, t2.id from salesrep t2
> WHERE t2.id = t1.rep1 or t2.id = t1.rep2) t3,
> WHERE t1=...
>
> I was hoping that it would parse the subquery like a nested
> loop and consequently return multiple values for t1 whenever a
> unique match in the t2 subquery was found, but this doesn't
> work.
>
> Is there any way to simulate an "OR" in an Oracle outer join
> without resorting to temporary tables or "extreme" measure?
>
> Thanks in advance,
>
> Bill Gerba
--
Galen Boyer
It seems to me, I remember every single thing I know.
Received on Wed Aug 29 2001 - 21:29:20 CDT
