1

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 :):

  1. the Oracle (+) Operator, by re-writing it to LEFT OUTER JOIN's
  2. 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).

enter image description here

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:

closed as unclear what you're asking by mustaccio, Laurenz Albe, MDCCL, John aka hot2use, Marcello Miorelli Sep 16 at 7:47

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

0

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.