Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

getting an error message (ORA-01417) when applying left join twice on the same table

tparvaizApr 7 2016 — edited Apr 11 2016

Hi,

I am applying left join twice on the same table and getting an error message

Here is my sample query (followed by the error message)

Select

                    H.*

FROM

                 TBL_A H

                 INNER JOIN TBL_B D  ON H.ID_NO = D.ID_NO

                 INNER JOIN TBL_C S   ON H.ID_NO = S.ID_NO

                 LEFT JOIN TBL_D DT   ON DT.ID_NO = H.ID_NO AND DT.G_NO = 1

                 LEFT JOIN TBL_D DTSL ON  DTSL.ID_NO = H.ID_NO AND DTSL.G_NO = D.ITEM_CAT

                 LEFT JOIN TBL_E LSL  ON D.ITEM_CAT = LSL.ID

           WHERE     H.ID_TYPE = 1

                 AND H.JOB_DATE > TO_DATE('2000-01-01', 'yyyy-mm-dd')

Here is the error message:

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


it works fine when I remove one of the Left join from tbl_D above



please advise

This post has been answered by Frank Kulash on Apr 8 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 9 2016
Added on Apr 7 2016
12 comments
2,126 views