I'm connected to an Oracle Database (11g Release 2 - 18.104.22.168).
I've inherited a chunk of SQL which apparently uses quite a lot of deprecated stuff and, importantly, "sometimes" yields wrong results (seems to be picking an outdated instance of data: my hunch here is that some of the many joins are buggy).
First off, I'm therefore trying to clean it up and then see why "wrong" results are (sometimes) produced.
My first ideas of what needs cleaning up is this (please feel free to add further ideas to the list :):
- the Oracle (+) Operator, by re-writing it to LEFT OUTER JOIN's
- the mix of "old, ancient and fragile implicit joins" by re-writing them as explicit JOIN's
Here goes an attempt at producing an equivalent of an MWE (minimal working example), though I don't have online data to share here by which this thing could actually fly. This is an attempt to take the main elements of the inherited code and syntax:
SELECT * FROM a1, a2, a3, ( select * from b3 where col4 in (select col5 from b6 where co7 = '007') ) c, a7 WHERE a1.col8 = c.col9 (+) AND c.col4 = a2.col5 (+) AND a2.bla = a3.bla AND a2.id = a7.id (+)
Here's the code above with the schemas present in the FROM section (if that's of any help?). In the WHERE section, the aliases are used already and no schema seems to be referenced...
Oracle seems to have a specific understanding of what a schema actually is... Below, SCHEMA_A would a name in the tree-branch-list under "Other Users" and a1 would be a table beneath that (on a twig of said branch).
SELECT * FROM SCHEMA_A.a1, SCHEMA_A.a2, SCHEMA_A.a3, ( select * from SCHEMA_B.b3 where col4 in (select col5 from b6 where co7 = '007') ) c, SCHEMA_A.a7 WHERE (...)
- https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52354 (Oracle recommending to not use the Oracle (+) operator, for several reasons)