Help needed to tidy up deprecated (and mal-functioning) SQL code I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4).



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 (...)




Related links:


https://stackoverflow.com/questions/23851976/left-outer-join-with-subquery-syntax
https://stackoverflow.com/questions/49549506/left-join-on-multiple-tables-error-table-or-view-does-not-exist
https://stackoverflow.com/questions/4020786/oracle-operator
https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52354 (Oracle recommending to not use the Oracle (+) operator, for several reasons)
First attempt at 1. (re-writing the Oracle (+) Operators as LEFT OUTER JOIN's); the subquery-part still isn't good though I guess; this is very-much draft-stage...

SELECT
    *
FROM
    a1

LEFT JOIN ( select * from b3 where col4 in
        (select col5 from b6 where col7 = '007')
  ) c ON a1.col8 = c.col9

LEFT JOIN a2 ON c.col4 = a2.col5

INNER JOIN a3 ON a2.bla = a3.bla

LEFT JOIN a7 ON a2.id = a7.id