This discussion is archived
6 Replies Latest reply: Jan 18, 2013 1:12 AM by 984640 RSS

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

984640 Newbie
Currently Being Moderated
Hi, I'm using Oracle 8i and could not use LEFT JOIN. So I need to use (+) instead.
But I'm encountering ORA-01417 for I.COL3 (+) =G.COL5

The complete command as follows:
SELECT *
FROM B, A, C, D, J, K, L, E, F, G, H, I
WHERE B.COL1='ABC'
AND B.COL1=A.COL1 AND B.COL2=A.COL2 AND B.COL3=A.COL3
AND C.COL1=B.COL1 AND C.COL2=B.COL6 AND C.COL3=B.COL7
AND B.COL1=D.COL1 (+) AND D.COL2 (+) =B.COL6
AND A.COL1=J.COL1 (+) AND A.COL2=J.COL2 (+)
AND B.COL1=K.COL1 (+) AND B.COL6=K.COL2 (+) AND B.COL11=K.COL3 (+) AND B.COL12=K.COL4 (+)
AND A.COL1=L.COL1 (+) AND A.COL2=L.COL2 (+) AND A.COL3=L.COL3 (+)
AND E.COL1 (+) = 'ABC' AND E.COL2 (+) =C.COL20 AND E.COL3 (+) =C.COL21
AND F.COL1 (+) = 'ABC' AND F.COL2 (+) =C.COL20
AND G.COL1 (+) = 'ABC' AND G.COL2 (+) =B.COL11 AND G.COL3 (+) =B.COL12
AND H.COL1 (+) = 'ABC' AND H.COL2 (+) ='NIP' AND H.COL3 (+) =G.COL5
AND I.COL1 (+) = 'ABC' AND I.COL2 (+) ='NIP' AND I.COL3 (+) =G.COL5* AND I.COL4 (+) =A.COL5 AND I.COL5 (+) ='0001'

Please advise.
Thanks in advance.
  • 1. Re: ORA-01417:a table may be outer joined to at most one other table
    APC Oracle ACE
    Currently Being Moderated
    You're using table G on both sides of an outer join:
    G.COL2(+)  =B.COL11 
    and
    I.COL3(+)  =G.COL5 
    That's not allowed (until 9i and the FULL OUTER JOIN syntax). So you will need to rewrite your query.

    Incidentally, you could have spotted that for yourself. Okay, it's a hairy query but whose fault is that?

    Cheers, APC

    P.S. Use the {noformat}
    {noformat} tags to bracket any code you post.  Not only does it make it more readable as code but it also prevents this dumb forum software turning {noformat}(+){noformat} into (+)
    
    Edited by: APC on Jan 16, 2013 9:14 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 2. Re: ORA-01417:a table may be outer joined to at most one other table
    BluShadow Guru Moderator
    Currently Being Moderated
    As APC says, you'll need ANSI syntax to achieve that.

    I sure hope your tables are not just called A, B, C, D and your columns not COL1, COL2 etc. otherwise someone's going to have a hard time following your queries.
    SELECT *
    FROM A            JOIN B ON (B.COL1=A.COL1
                            AND  B.COL2=A.COL2
                            AND  B.COL3=A.COL3)
                      JOIN C ON (C.COL1=B.COL1
                            AND  C.COL2=B.COL6
                            AND  C.COL3=B.COL7)
           LEFT OUTER JOIN D ON (B.COL1=D.COL1
                            AND  B.COL6=D.COL2)
           LEFT OUTER JOIN E ON (E.COL1='ABC'
                            AND  E.COL2=C.COL20
                            AND  E.COL3=C.COL21)
           LEFT OUTER JOIN F ON (F.COL1='ABC'
                            AND  F.COL2=C.COL20)
           LEFT OUTER JOIN G ON (G.COL1='ABC'
                            AND  G.COL2=B.COL11
                            AND  G.COL3=B.COL12)
           LEFT OUTER JOIN H ON (H.COL1='ABC'
                            AND  H.COL2='NIP'
                            AND  H.COL3=G.COL5)
           LEFT OUTER JOIN I ON (I.COL1='ABC'
                            AND  I.COL2='NIP'
                            AND  I.COL3=G.COL5
                            AND  I.COL4=A.COL5
                            AND  I.COL5='0001')
           LEFT OUTER JOIN J ON (A.COL1=J.COL1
                            AND  A.COL2=J.COL2)
           LEFT OUTER JOIN K ON (B.COL1=K.COL1
                            AND  B.COL6=K.COL2
                            AND  B.COL11=K.COL3
                            AND  B.COL12=K.COL4)
           LEFT OUTER JOIN L ON (A.COL1=L.COL1
                            AND  A.COL2=L.COL2
                            AND  A.COL3=L.COL3)
    WHERE B.COL1='ABC'
    Edited by: BluShadow on 16-Jan-2013 09:57
    Also, as APC points out, there could be a full outer join in there, but as I was going dizzy with all the A, B, C, D and COL1, COL2 etc. I wasn't going to actually try and follow the code too closely. The principle is given above anyway.
  • 3. Re: ORA-01417:a table may be outer joined to at most one other table
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Since you can't use ANSI syntax, you can do some of the joins (at least A, B and G) in an in-line view, and then join table I to that result set.

    Whenever you have a problem, please post a complete test script that people can run to re-create the problem and test their ideas. Include CREATE TABLE and INSERT statements for all tables involved, and the results you want from that data. Explain how you get the results you posted from the data you posted.
    Simplify the problem as much as possibile. For example, you'd probably have the same problem with fewer tables. The core of this problem is how to outer-join table I to tables A and G. Can you post a problem that only involves those 3 tables? Maybe not, since the relationship between A and G is important, and you need table B for that, but it looks like those 4 tables are all you need to illustrate the problem. You already seem to know how to deal with the other 8 tables, so there's no need to include them in the sample data and results.
  • 4. Re: ORA-01417:a table may be outer joined to at most one other table
    984640 Newbie
    Currently Being Moderated
    Hi Frank, Yes, you're right, I only need 4 tables to show the problem.

    To simplify:
    Table A:
    KeyS = COL1, COL2 & COL3
    COL4 & COL5 are needed to link to Table C

    Table B:
    KeyS = COL1, COL2 & COL3
    COL4 is needed to link to Table D

    Table C:
    KeyS = COL1, COL2 & COL3
    COL4 is needed to link to Table D

    Table D:
    KeyS = COL1, COL2, COL3 & COL4
    To retrieve COL5

    So the following script will have problem:
    SELECT * FROM A, B, C, D
    WHERE B.COL1=A.COL1 AND B.COL2=A.COL2 AND B.COL3=A.COL3
    AND C.COL1 (+) ='ABC' AND C.COL2 (+) = A.COL4 AND C.COL3 (+) = A.COL5
    AND D.COL1 (+) ='ABC' AND D.COL2 (+) ='LOC' AND D.COL3 (+) = C.COL4 AND D.COL4 (+) = B.COL4

    How to re-write the script according to your suggestion?
    Thanks in advance!
  • 5. Re: ORA-01417:a table may be outer joined to at most one other table
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    981637 wrote:
    ... How to re-write the script according to your suggestion?
    Maybe something like:
    SELECT  * 
    FROM      (     -- Begin in-line view abc
              SELECT     b.col4      AS b_col4
              ,     c.col4      AS c_col4
         --     ,     ...      -- Whatever columns you want to display in the main query
              FROM     a
              ,      b
              ,      c
              WHERE      b.col1          = a.col1 
              AND     b.col2          = a.col2 
              AND     b.col3          = a.col3
              AND     c.col1 (+)      = 'ABC' 
              AND     c.col2 (+)      = a.col4 
              AND     c.col3 (+)      = a.col5
         ) abc     -- End in-line view
    ,     d
    WHERE     d.col1 (+)     = 'ABC' 
    AND     d.col2 (+)      = 'LOC' 
    AND     d.col3 (+)      = abc.c_col4
    AND     d.col4 (+)      = abc.b_col4
    ;
    I can't say for sure without some sample data (CREATE TABLE and INSERT statements) and the results you want from that data.

    In your real problem, the other tables can be joined to a, b and c in the subquery (I'm guessing that this will be the more convenient way to do it), or they can be joined to abc in the main query.
  • 6. Re: ORA-01417:a table may be outer joined to at most one other table
    984640 Newbie
    Currently Being Moderated
    Thanks Frank, it solves the problem!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points