Home » SQL & PL/SQL » SQL & PL/SQL » A Join with more than on table
A Join with more than on table [message #563717] Thu, 16 August 2012 10:57 Go to next message
bennani1974@hotmail.com
Messages: 7
Registered: August 2012
Junior Member
Hi everybody

I use a table taht i call C where the value of a field ("Type") is always a concat of values coming from more than 2 tables (A et C)
select A.Numero, B.date, B.commentaire,C.Libelle
from A, B, C
where A.codeLibelle = C.codeLibelle
and CONCAT(A.Numero, CONCAT(A.DemNumero, C.Libelle)) = B.Type (+)

when i execute this statement, i obtain
ORA-01417: a table may be outer joined to at most one other table

I have another request where it works fine and where i have concat of fields from only a single table:

select A.Numero, B.date, B.commentaire,C.Libelle
from A, B
where CONCAT(A.Numero, A.DemNumero) = B.Type (+)

In the first request Oracle seems to not accept a join with more than 2 tables

Thanks for help

Laymo
Re: A Join with more than on table [message #563720 is a reply to message #563717] Thu, 16 August 2012 11:02 Go to previous messageGo to next message
BlackSwan
Messages: 22722
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: A Join with more than on table [message #563723 is a reply to message #563720] Thu, 16 August 2012 11:10 Go to previous messageGo to next message
bennani1974@hotmail.com
Messages: 7
Registered: August 2012
Junior Member
sorry i may have forgot the version of Oracle i use and to put the error number in the title
Oracle 10g
ORA-01417: a table may be outer joined to at most one other table
Re: A Join with more than on table [message #563724 is a reply to message #563723] Thu, 16 August 2012 11:19 Go to previous messageGo to next message
BlackSwan
Messages: 22722
Registered: January 2009
Senior Member
10g is NOT a version.
It is a marketing label.
SELECT * FROM V$VERSION;
bcm@bcm-laptop:~$ 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.
Re: A Join with more than on table [message #563726 is a reply to message #563723] Thu, 16 August 2012 11:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
In second example B.Type is compared to expression involviong single table A while in first example B.Type is compared to expression involviong two tables A and C. Therefore second example fails. Use either:

select  D.Numero,
        B.date,
        B.commentaire,
        D.Libelle
  from  (
         select  A.Numero,
                 A.DemNumero,
                 C.Libelle
           from  A,
                 C
           where A.codeLibelle = C.codeLibelle
        ) D,
        B
  where CONCAT(D.Numero,CONCAT(D.DemNumero,D.Libelle)) = B.Type(+)
/


Or use ANSI joins:

select  A.Numero,
        B.date,
        B.commentaire,
        C.Libelle
  from      A
        join
            C
          on A.codeLibelle = C.codeLibelle
        left join
            B
          on CONCAT(A.Numero,CONCAT(A.DemNumero,C.Libelle)) = B.Type
/


SY.
Re: A Join with more than on table [message #563727 is a reply to message #563726] Thu, 16 August 2012 11:34 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
One more thing : date is reserved word, and type is a keyword so don't use them as column names.

SY.
P.S. It should be "Therefore first example fails." in my first reply.

[Updated on: Thu, 16 August 2012 11:37]

Report message to a moderator

Re: A Join with more than on table [message #563742 is a reply to message #563727] Thu, 16 August 2012 13:06 Go to previous messageGo to next message
bennani1974@hotmail.com
Messages: 7
Registered: August 2012
Junior Member
your first solution works perfectly, but when i tried the second one it still gives me the same error ORA-01417
Anyway thank you very much Solomon
Re: A Join with more than on table [message #563744 is a reply to message #563742] Thu, 16 August 2012 13:42 Go to previous message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
Prove it.

SQL> select  A.Numero,
  2          B.dt,
  3          B.commentaire,
  4          C.Libelle
  5    from      A
  6          join
  7              C
  8            on A.codeLibelle = C.codeLibelle
  9          left join
 10              B
 11            on CONCAT(A.Numero,CONCAT(A.DemNumero,C.Libelle)) = B.Type
 12  /

no rows selected

SQL> 


As you can see, not errors, I just have no data in the tables.

SY.
P.S. Post your full version.
Previous Topic: REG:CONVERTING A CLOB DATATYPE to VARCHAR datatype
Next Topic: getting error in cimple cursor
Goto Forum:
  


Current Time: Mon Sep 01 07:13:19 CDT 2014

Total time taken to generate the page: 0.14441 seconds