Join issue [message #425104] |
Wed, 07 October 2009 10:06  |
sekharsomu
Messages: 72 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 #425112 is a reply to message #425104] |
Wed, 07 October 2009 10:40   |
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
|
|
|
|
|