[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2014-01-08
9
Medium Priority
?
1,552 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 25

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 11

Expert Comment

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

Open in new window

0
 
LVL 14

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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
 
LVL 14

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 143

Expert Comment

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

650 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