hmstechsupport
asked on
Oracle T-SQL fails with ORA-01416 but I do not know why
I have the following sql:
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?
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=0This 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?
Try
...
PSE_PSL(+)=0
...![Avatar of Alex [***Alex140181***]](https://filedb.experts-exchange.com/files/public/2022/11/13/8257e912-ee3c-4a93-9df0-fe9a8a1515cf.jpeg){kind=small}
PSE_PSL(+)=01. 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...
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)?!
ASKER
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.
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.
ASKER
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.
ASKER
The solution to add the alias was correct which is why I'm awarding all points to Guy.
glad I could help
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:
Open in new window
BTW, T-SQL is the SQL version of MS SQL Server. Oracle calls theirs PL/SQL