[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1578
  • Last Modified:

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

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
hmstechsupport
Asked:
hmstechsupport
  • 3
  • 2
  • 2
  • +2
1 Solution
 
chaauCommented:
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
 
HuaMinChenBusiness AnalystCommented:
Try
...
PSE_PSL(+)=0
...

Open in new window

0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
hmstechsupportAuthor Commented:
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
 
hmstechsupportAuthor Commented:
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
 
hmstechsupportAuthor Commented:
The solution to add the alias was correct which is why I'm awarding all points to Guy.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
glad I could help
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now