Solved

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

Posted on 2014-01-08
9
1,405 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Read about achieving the basic levels of HRIS security in the workplace.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

943 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

9 Experts available now in Live!

Get 1:1 Help Now