quote from :
----------------------------------------------------------------------
Thanks Art, but i was looking for some more details explaination like how will an left outer/right outer join will behave if there are more than one table involved in the join.
Tia
-R
----------------------------------------------------------------------
Oh, your original post didn't happen to mention that.
Can you give a specific example of what you're trying to do?
When it comes to outer joins in Oracle, there are usually two gotchas I run into:- ORA-01417 errors;
- The invalidation of outer joins by not chaining them correctly.
To fix the ORA-1417, first create an in-line view, and then outer-join to that. The error description covers this fix pretty well:$ oerr ora 1417
01417, 00000, "a table may be outer joined to at most one other table"
// *Cause: a.b (+) = b.b and a.c (+) = c.c is not allowed
// *Action: Check that this is really what you want, then join b and c first
// in a view.
$
As for the second "gotcha". You have to chain your outer joins. For example, this querySQL> SELECT d.dname
2 , COUNT(e.ename)
3 FROM emp e
4 , dept d
5 WHERE d.deptno = e.deptno (+)
6 AND e.ename > CHR(32)
7 GROUP BY d.dname
8 /
will not produce thisDNAME COUNT(E.ENAME)
-------------- --------------
ACCOUNTING 3
OPERATIONS 0
RESEARCH 5
SALES 6
but instead will produce this DNAME COUNT(E.ENAME)
-------------- --------------
ACCOUNTING 3
RESEARCH 5
SALES 6
To get the former, you will have to chain your outer joins:SQL> SELECT d.dname
2 , COUNT(e.ename)
3 FROM emp e
4 , dept d
5 WHERE d.deptno = e.deptno (+)
6 AND e.ename <font color=red><b>(+)</b></font> > CHR(32)
7 GROUP BY d.dname
8 /
DNAME COUNT(E.ENAME)
-------------- --------------
ACCOUNTING 3
OPERATIONS 0
RESEARCH 5
SALES 6
SQL>
See here for an explanation.
If these points do not cover your issue, Rock, please provide a simple example.
HTH,
Art.