Home » SQL & PL/SQL » SQL & PL/SQL » Join issue (oracle 9.i ,xp)
Join issue [message #425104] Wed, 07 October 2009 10:06 Go to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
Hi guys

i know that its better to use views for getting data from multiple select statements which already have a join in them. but as i am working with some GIS maps views are not recognized. so, i have to club all those select queries into one.
Now, comes the problem there are around 9 tables i made a join like we do for any 3 or more multiple join and have provide a transitive chain between the common column among this 9 tables
when i execute it in developer it gives me the following error:

SQL Error: ORA-01417: a table may be outer joined to at most one other table
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.


is there a way to join 9 tables without using a view.
Sorry that i could not publish the actually content. the code has some properitory terminology
Thanks in advance.
Re: Join issue [message #425109 is a reply to message #425104] Wed, 07 October 2009 10:22 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
One other option would be to re-write the query into ANSI JOIN syntax.
Re: Join issue [message #425112 is a reply to message #425104] Wed, 07 October 2009 10:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You don't have to use a view to join the table - you can use an inline view in the where clause:
create table test_081 (col_1  number);
create table test_082 (col_1  number);
create table test_083 (col_1  number);

insert into test_081 values (1);
insert into test_081 values (2);
insert into test_081 values (3);

insert into test_082 values (1);
insert into test_082 values (2);

insert into test_083 values (1);

select t1.col_1,t2.col_1,t3.col_1
from   test_081 t1
      ,test_082 t2
      ,test_083 t3
where  t1.col_1 = t2.col_1
and    t1.col_1 = t3.col_1(+)
and    t2.col_1 = t3.col_1(+);

ERROR at line 6:
ORA-01417: a table may be outer joined to at most one other table

select t1_col_1,t2_col_1,t3.col_1
from   (select vt1.col_1  t1_col_1
              ,vt2.col_1  t2_col_1
        from   test_081 vt1
              ,test_082 vt2
        where  vt1.col_1 = vt2.col_1) vw
      ,test_083 t3
where  vw.t1_col_1 = t3.col_1(+)
and    vw.t2_col_1 = t3.col_1(+);

  T1_COL_1   T2_COL_1      COL_1
---------- ---------- ----------
         1          1          1
         2          2
Re: Join issue [message #425133 is a reply to message #425112] Wed, 07 October 2009 12:38 Go to previous messageGo to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
Thanks for the inline view suggestion but can u explain me this "(+)" what does it mean ?
Re: Join issue [message #425136 is a reply to message #425133] Wed, 07 October 2009 12:57 Go to previous message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
It is Oracle's original way to denote an ANSI OUTER JOIN
Previous Topic: merging row in a query
Next Topic: fastest way to update value
Goto Forum:
  


Current Time: Sun Sep 25 19:41:59 CDT 2016

Total time taken to generate the page: 0.21978 seconds