6 Replies Latest reply: Jan 18, 2013 3:12 AM by 984640 RSS

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

    984640
      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
          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
            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
              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
                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
                  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
                    Thanks Frank, it solves the problem!