Does Oracle correlate a subquery nested at any level?

Question

I see on different questions to this forum about this same topic, that a canned and frequently appearing response is:

No, Oracle doesn't correlate the subqueries nested more than one level deep (and neither does MySQL).

I would conclude from this that Oracle does correlate a subquery at one level deep. However, I have the below query, and it returns this error message:

ORA-00904: "CD"."FIELD6": invalid identifier

This query is important to be fashioned as it is, and is only a simplified version of the actual query which involves a UNION statement. When debugging why the error message was being returned, I reduced it to be this most simple version (below). I realize there may be alternate JOIN approaches to this most simple version, but such alternate approaches would not play out for the actual query. If the below can work, then seemingly our more complex query can also work. If the below can't work, then what am I reading incorrectly within Oracle's documentation and the "canned" answer above?

SELECT a.*
FROM
    main_detail cd INNER JOIN
    (
    SELECT
        Field1,
        Field2,
        Field3,
        Field4,
        Field5,
        Field6,
        Field7
    FROM other_detail x2
    WHERE x2.Field1 = cd.Field1 AND x2.Field6 = cd.Field6
    ) a ON
    a.Field1 = cd.Field1
    AND a.Field4 = cd.Field4
    AND a.Field6 = cd.Field6

The below is more similar to our actual need. The UNION needs to be as it is to allow the JOIN on the UNION-ed set returned, for which is being used as an INNER JOIN to limit the return record set:

SELECT h.*, a.*
FROM
    header h,
    (
    SELECT
        Field1,
        Field2,
        Field3,
        Field4,
        Field5,
        Field6,
        Field7
    FROM main_detail x1
    WHERE x1.Field1 = h.Field1 AND x1.Field6 = h.Field6
    UNION
    SELECT
        Field1,
        Field2,
        Field3,
        Field4,
        Field5,
        Field6,
        Field7
    FROM other_detail x2
    WHERE x2.Field1 = h.Field1 AND x2.Field6 = h.Field6
    ) a
    WHERE
        a.Field1 = h.Field1 AND
        a.Field6 = h.Field6

When comparing the above to not including the correlated subquery parameters, and testing the run in MS SQL, it improves the performance from 9 minutes to be 30-40 seconds, a very considerable improvement. I was hoping to experience the same gains in Oracle.

Below is as close as I can do for the actual code, without compromising client confidentiality:

SELECT DISTINCT 
    c.Field1,
    c.Field2,
    D.Field3,
    b.Field4,
    b.Field5,
    c.Field6,
    c.Field7 || '-' || cds1.Field8 AS status,
    b.paid,
    cds.Field8,
    p.Field9,
    p.Field10,
    c.Field11,
    c.Field12 AS provider_name
FROM
    header c,
    (
    SELECT
        a.*,
        cd.paid,
        cd.Field15,
        cd.BigList,
        cd.allowed,
        cd.copayment,
        cd.coinsurance
    FROM
        header_detail cd,
        (
        SELECT
            Field1,
            Field4,
            '' AS revenue_code,
            Field20,
            Field5,
            Field14,
            location_code,
            ServiceList
        FROM header_other_detail x1
        WHERE x1.Field1 = cd.Field1 AND x1.Field14 = cd.Field14
        UNION
        SELECT
            Field1,
            Field4,
            revenue_code,
            Field20,
            Field5,
            Field14,
            '' AS location_code,
            ServiceList
        FROM inst_claim_detail x2
        WHERE x2.Field1 = cd.Field1 AND x2.Field14 = cd.Field14
        ) a
    WHERE
        a.Field1 = cd.Field1
        AND cd.Field1 = c.Field1
        AND a.Field20 = cd.Field20
        AND a.Field14 = cd.Field14
        AND cd.Field14 = c.Field14a
    ) b,
    (
    SELECT
        Field1,
        Field14,
        Trim(
            Trailing ',' FROM
            ch.icd9_1  || ',' ||
            ch.icd9_2  || ',' ||
            ch.icd9_3  || ',' ||
            ch.icd9_4  || ',' ||
            ch.icd9_5  || ',' ||
            ch.icd9_6  || ',' ||
            ch.icd9_7  || ',' ||
            ch.icd9_8  || ',' ||
            ch.icd9_9  || ',' ||
            ch.icd9_10 || ',' ||
            ch.icd9_11 || ',' ||
            ch.icd9_12
            )
        AS Field3
    FROM prof_claim ch
    WHERE ch.Field1 = c.Field1 AND ch.Field14 = c.Field14a
    UNION
    SELECT
        Field1,
        Field14,
        Field3
    FROM inst_claim x3
    WHERE x3.Field1 = c.Field1 AND x3.Field14 = c.Field14a
    ) d,
    (
    SELECT
        Field1,
        Field14,
        Field9,
        Field10,
        Field18,
        refund_amount,
        Field15
    FROM payment_detail
    ) p,
    (SELECT * FROM Codes WHERE code_type='19') cds,
    (SELECT * FROM Codes WHERE code_type='28') cds1
WHERE
    c.Field17 = 'T00000370'
    AND c.Field1 = b.Field1 AND c.Field14a = b.Field14
    AND c.Field1 = d.Field1 AND c.Field14a = d.Field14
    AND b.Field14 = p.Field14(+) AND b.Field1 = p.Field1(+) AND b.Field15 = p.Field15(+)
    AND b.BigList = cds.Field16(+) AND b.Field14 = cds.Field14(+)
    AND c.Field7 = cds1.Field16(+) AND c.Field14a = cds1.Field14(+)
ORDER BY Field1;

Answer 1

If you're joining a subquery to a table (or another subquery) then all the join conditions must be in the ON clause, if you're using ANSI style joins.

The reason why your query is failing is because the a subquery's scope does not extend outside of itself. It is entirely separate to the scope of the main_detail table, as you are trying to join the tables, not correlate them. As Marmite Bomber's answer suggests, the subquery needs to be able to run on its own, which your attempted version doesn't.

What you need to do is move the correlated filters to the ON clause, like so:

SELECT a.*
FROM
    main_detail cd INNER JOIN
    (
    SELECT
        Field1,
        Field2,
        Field3,
        Field4,
        Field5,
        Field6,
        Field7
    FROM other_detail x2) a ON
    a.Field1 = cd.Field1
    AND a.Field4 = cd.Field4
    AND a.Field6 = cd.Field6
    and a.Field1 = cd.Field1 AND a.Field6 = cd.Field6

which is, of course, identical to:

SELECT a.*
FROM
    main_detail cd INNER JOIN
    other_detail a ON
      a.Field1 = cd.Field1
      AND a.Field4 = cd.Field4
      AND a.Field6 = cd.Field6

---

With regards to your query with the UNION, this should help give you some performance benefit over your original query:

SELECT *
FROM   (SELECT h1.*,
               x1.field1,
               x1.field2,
               x1.field3,
               x1.field4,
               x1.field5,
               x1.field6,
               x1.field7
        FROM   main_detail x1
               inner join header h1 on (x1.Field1 = h1.Field1 AND x1.Field6 = h1.Field6)
        UNION
        SELECT h2.*
               field1,
               field2,
               field3,
               field4,
               field5,
               field6,
               field7
        FROM   other_detail x2
               inner join header h2 on (x2.Field1 = h2.Field1 AND x2.Field6 = h2.Field6));

Answer 2

In the subquery

SELECT
    Field1,
    Field2,
    Field3,
    Field4,
    Field5,
    Field6,
    Field7
FROM other_detail x2
WHERE x2.Field1 = cd.Field1 AND x2.Field6 = cd.Field6

check the where condition. If removed completely or at least removing the cdreference - the query works.