Porting OUTER JOIN statements to Oracle 8i

From: Bill Gerba <bill_at_wirespring.com>
Date: 8 Aug 2001 13:11:02 -0700
Message-ID: <516329be.0108081211.162432f0@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)

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 Received on Wed Aug 08 2001 - 15:11:02 CDT