Solved

Oracle T-SQL fails with ORA-01416 but I do not know why

Posted on 2014-01-08
9
1,379 Views
Last Modified: 2014-01-09
I have the following sql:

SELECT * FROM EMPHIS 

LEFT JOIN TCNOTES EMPHISNOTES ON TCN_FIELD='EMH_NOTES' AND TCN_AKEY=EMH_KEY 
LEFT JOIN EMPLOYEE ON EMP_KEY=EMH_EMP 
LEFT JOIN TCRES ON RES_KEY=EMH_RES 
LEFT JOIN TCNOTES TCRESNOTES ON TCRESNOTES.TCN_FIELD='RES_NOTES' AND TCRESNOTES.TCN_AKEY=RES_KEY 

LEFT JOIN PSHEADER ON PSH_EMH=EMH_KEY LEFT JOIN TCNOTES PSHEADERNOTES ON PSHEADERNOTES.TCN_FIELD='PSH_NOTES' AND PSHEADERNOTES.TCN_AKEY=PSH_KEY LEFT JOIN PSLINES ON PSL_PSH=PSH_KEY LEFT JOIN TCRES TLRES ON TLRES.RES_KEY=PSLINES.PSL_RES 
LEFT JOIN ACTIVITY ON ACT_KEY=PSL_ACT LEFT JOIN TCNOTES PSLINESNOTES ON PSLINESNOTES.TCN_FIELD='PSL_NOTES' AND PSLINESNOTES.TCN_AKEY=PSL_KEY LEFT JOIN TCPROJ ON PRJ_KEY=PSL_PRJ 
LEFT JOIN TCNOTES TCPROJNOTES ON TCPROJNOTES.TCN_FIELD='PRJ_NOTES' AND TCPROJNOTES.TCN_AKEY=PRJ_KEY LEFT JOIN TCUSER ON USR_KEY=PRJ_OWNER 
LEFT JOIN TCNOTES TCUSERNOTES ON TCUSERNOTES.TCN_FIELD='USR_NOTES' AND TCUSERNOTES.TCN_AKEY=USR_KEY 

JOIN CHRHIS ON CHH_KEY=PSL_CHH 
LEFT JOIN TCNOTES CHRHISNOTES ON CHRHISNOTES.TCN_FIELD='CHH_NOTES' AND CHRHISNOTES.TCN_AKEY=CHH_KEY LEFT JOIN CHARGE ON CHR_KEY=CHH_CHR 
LEFT JOIN PSDETAIL ON PSD_PSL=PSL_KEY 
LEFT JOIN PSEXPENS ON PSE_PSL=0

Open in new window




This code for some reason fails with

ORA-01416: two tables cannot be outer-joined to each other

However, I do not see where two tables are outer-join to each other.

If I remove or comment the [LEFT JOIN PSEXPENS ON PSE_PSL=0] it works fine.

If I change it to [LEFT JOIN PSEXPENS ON PSE_PSL=PSL_KEY] it works fine.

Why does PSE_PSL=0 cause the ORA-01416?
0
Comment
Question by:hmstechsupport
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 39766804
FYI, "LEFT JOIN" is a shortcut to the statement "LEFT OUTER JOIN". So, all your tables are outer-joined.
Why don't you want to specify any condition for the PSEXPENS join? It seems incorrect. The table should be joined logically. I think there is a functional error in your join. Perhaps you wanted this:
LEFT JOIN PSEXPENS ON PSE_PSL=PSL_KEY AND PSE_PSL=0

Open in new window

BTW, T-SQL is the SQL version of MS SQL Server. Oracle calls theirs PL/SQL
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39767077
Try
...
PSE_PSL(+)=0
...

Open in new window

0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39767409
PSE_PSL(+)=0
1. This is the same as above, just using Oracle's native join = LEFT OUTER JOIN, so it should not matter if you use this or the other one
2. Avoid mixing join styles (ANSI + Oracle native)! Moreover, try to use ANSI all the time. E.G.: if you want to migrate to another (SQL aware) DB, you'll get problems with the native style...
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39767438
>LEFT JOIN PSEXPENS ON PSE_PSL=0

note that you may have another table with that field name, in which case the other table may take some priority. see this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html

so, this should work correctly:
LEFT JOIN PSEXPENS ON PSEXPENS.PSE_PSL=0


I agree with above that LEFT JOIN is the same as LEFT OUTER JOIN, so do you really need this outer join? or shall it be INNER JOINs ( JOIN without LEFT or RIGHT is a INNER JOIN) ...
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39767489
Yes, you should defintely use aliases in your statement. Apart from that: what kind of DB object do you refer to within your from list (I mean are they tables and/views)?!
0
 

Author Comment

by:hmstechsupport
ID: 39768063
Everything in the SQL is a table. The reason we do PSE_PSL=0 is because we have a UNION that joins data from this table and here is why:

We have a table PSLINES, PSDETAIL, and PSEXPENS. The relationships are as follows:

PSLINES : PSL_KEY -> PSD_PSL : PSDETAIL (one to many)
PSLINES : PSL_KEY -> PSE_PSL : PSEXPENS (one to many)

Now, the above SQL statement feeds a reporting module so we use a UNION:

FROM PSLINES LEFT JOIN PSDETAIL ON PSD_PSL=PSL_KEY LEFT JOIN PSEXPENS ON PSE_PSL=0
-- in this case we only want to get the PSDETAIL records
UNION
FROM PSLINES LEFT JOIN PSDETAIL ON PSD_PSL=0 LEFT JOIN PSEXPENS ON PSE_PSL=PSL_KEY
-- in this case we only to get the PSEXPENS records

If we don't do this UNION we have potential to end up with inaccurate data. I agree we are victim of a less than stellar database design but at this moment in time we are unable to change the structure. Also, this SQL works perfect in SQL Server and MySQL; Oracle is the only database that throws an error.

Now, I've just done the test by adding PSEXPENS.PSE_PSL=0 and it corrects the issue. We do normally always use aliases as you can see from the majority of the SQL statement that we do have aliases so it seems we need to be more aggressive with it.
0
 

Author Comment

by:hmstechsupport
ID: 39768078
Just to add, as I said, this SQL is designed to work on multiple databases (SQL Server, MySQL, Oracle) and this SQL is actually generated at run time dynamically (this isn't a hard coded SQL statement, we have a dictionary and a complex piece of code that puts this SQL together). This is why the SQL may seem a little unorthodox to an Oracle DBA and why we don't always have aliases.
0
 

Author Closing Comment

by:hmstechsupport
ID: 39768080
The solution to add the alias was correct which is why I'm awarding all points to Guy.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39768098
glad I could help
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now