Home » SQL & PL/SQL » SQL & PL/SQL » Problem with join
icon9.gif  Problem with join [message #247382] Mon, 25 June 2007 14:07 Go to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

Hi, i have a problem when i am doing a join
...
63 from
64 edu_pp_experiencias exp,
65 edu_pp_cue_exper cuee,
66 edu_pp_parte_de part,
67 edu_pp_destinat_por_edad edad,
68 edu_pp_destinat_caract carac,
69 edu_pp_participantes parti,
70 edu_pp_integracion inte,
71 edu_pp_cant_particip cant,
72 edu_pp_adultos adul,
73 edu_pp_organizaciones orga,
74 edu_pp_evaluadores eval,
75 edu_pp_fuentes_recursos frec,
76 edu_pp_materiales_exp mexp
77 where
78 exp.exp_id(+)=cuee.exp_id and
79 exp.exp_id(+)=part.exp_id and
80 exp.exp_id(+)=edad.exp_id and
81 exp.exp_id(+)=carac.exp_id and
82 exp.exp_id(+)=parti.exp_id and
83 exp.exp_id(+)=inte.exp_id and
84 exp.exp_id(+)=cant.exp_id and
85 exp.exp_id(+)=adul.exp_id and
86 exp.exp_id(+)=orga.exp_id and
87 exp.exp_id(+)=eval.exp_id and
88 exp.exp_id(+)=frec.exp_id and
89 exp.exp_id(+)=mexp.exp_id and
90 trunc(exp.exp_fecha_carga)>='1-JAN-2007'
91 /
exp.exp_id(+)=frec.exp_id and
*
ERROR at line 88:
ORA-01417: a table may be outer joined to at most one other table


does anyone have any idea why want to use outher join?
thnx in advance
Re: Problem with join [message #247383 is a reply to message #247382] Mon, 25 June 2007 14:11 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
thorin666 wrote on Mon, 25 June 2007 15:07
Hi, i have a problem when i am doing a join
...

89 exp.exp_id(+)=mexp.exp_id and
90 trunc(exp.exp_fecha_carga)>='1-JAN-2007'
91 /
exp.exp_id(+)=frec.exp_id and
*
ERROR at line 88:
ORA-01417: a table may be outer joined to at most one other table


does anyone have any idea why want to use outher join?
thnx in advance



I don't understand the question. You want to know why one would use an outer join?
As far as your error:
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.

Additionally, Line 90 is invalid and you cannot compare a DATE column to a character string.

Re: Problem with join [message #247388 is a reply to message #247382] Mon, 25 June 2007 14:26 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
does anyone have any idea why want to use outher join?

Do you have any idea what OUTER JOIN is?

Seems you swapped LEFT JOIN (you want) with RIGHT JOIN (you use). See the given link for their meaning (and difference).
Re: Problem with join [message #247389 is a reply to message #247388] Mon, 25 June 2007 14:31 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

sorry for my english, i want to say why the error msg says that want to use outher join
i want to use left join because i want all the data from all tables that are in edu_pp_experiencias

again sorry for my english
Re: Problem with join [message #247409 is a reply to message #247382] Mon, 25 June 2007 15:16 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
It's not problem of english.
It's problem of uderstanding the statement.

Assuming you statement will work - you are generating CARTESIAN product from ALL your tables (except edu_pp_experiencias ).

IMHO - flyboy is right and you are confusing LEFT and RIGHT outer joins.

Anyway because you have a condition
trunc(exp.exp_fecha_carga)>='1-JAN-2007'

Outer join will NOT work anyway.

Eiter move (+) sigh to the other side of each condition or remove it completely.

Michael
Re: Problem with join [message #247592 is a reply to message #247409] Tue, 26 June 2007 08:14 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

sorry but is not the same put something like this?

select A.colx, B.coly
from table1 A,table2 B
where
A.col1(+)=B.col1;

and

select A.colx, B.coly
from table1 A
left join table2 B
on (A.col1=B.col1);

maybe i am wrong i want to do the 2nd example


Re: Problem with join [message #247597 is a reply to message #247382] Tue, 26 June 2007 08:28 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
no, it is not. seems you either did not follow the link I gave about OUTER JOIN or you are totally incapable to understand it.
Quote:
To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.

You apply (+) to the column of table A. That is RIGHT JOIN as I mentioned before.
LEFT JOIN is
select A.colx, B.coly
from table1 A,table2 B
where
A.col1=B.col1(+);

However, if your Oracle version supports JOIN syntax, why do you not use it instead of join operator (+)?
Re: Problem with join [message #247598 is a reply to message #247597] Tue, 26 June 2007 08:30 Go to previous message
thorin666
Messages: 144
Registered: March 2007
Senior Member

sorry i readed after post Embarassed
ill try thnx for your help Very Happy
Previous Topic: Displaying the Results based on the attached excel formulae. do not answer me, i am willing to wait.
Next Topic: set values to timestamp .
Goto Forum:
  


Current Time: Sat Dec 10 03:11:13 CST 2016

Total time taken to generate the page: 0.10459 seconds