Is there a workaround to the Oracle Correlated Subquery Nesting Limit?

Question

I have a situation where I'm trying to use a correlated subquery but am running into the nesting limit in Oracle. I might be missing another feature that Oracle has, so I thought I'd post this question here. Does anyone know how to rewrite the below SQL without running into this nesting limit, but also staying within the below constraints?

Constraints:

Only the SQL in the IN clause can be modified (Due to constraints beyond my control)
As shown, the filtering in the parent query needs to be applied to the aggregation subquery before the aggregation occurs.
Filter out 0 on an aggregation of colB after the parent filter is applied

The below code shows my try at this before running into the Oracle limit. Also, the Oracle version I'm on is 11.2.0.2. Any help would be appreciated. Thanks!

SELECT
  *
FROM
  table1 t1
WHERE
  t1.colA BETWEEN XXXX AND XXXX 
  AND t1.pk_id IN (
    SELECT
      t2.pk_id
    FROM (
      SELECT
        t3.pk_id,
        SUM(t3.amt) OVER (PARTITION BY t3.colB) amt
      FROM table1 t3
        WHERE t3.colA = t1.colA
    ) t2
    WHERE
      t2.amt <> 0
  )

Here are some sample input/outputs of what I was looking for when running the above SQL:

Sample table1:
-----------------------------
| pk_id | colA | colB | amt |
-----------------------------
|   1   |  1   |  A   |  2  |
|   2   |  1   |  A   |  -1 |
|   3   |  1   |  B   |  1  |
|   4   |  2   |  B   |  1  |
|   5   |  2   |  A   |  -2 |
|   6   |  2   |  A   |  1  |
|   7   |  3   |  A   |  1  |

Results of SUM over t3.colB with t1.colA BETWEEN 1 And 2:

---------------
| pk_id | amt |
---------------
|   1   |  0  |
|   2   |  0  |
|   3   |  2  |
|   4   |  2  |
|   5   |  0  |
|   6   |  0  |

Results of subquery for IN clause with t1.colA BETWEEN 1 And 2:

---------
| pk_id |
--------- 
|   3   |
|   4   |

Result of top level query with t1.colA BETWEEN 1 And 2:

-----------------------------
| pk_id | colA | colB | amt |
-----------------------------
|   3   |  1   |  B   |  1  |
|   4   |  2   |  B   |  1  |

After working through some of the answers provided, I have a way of avoiding the nesting limit in Oracle with a simple CASE statement:

SELECT
  *
FROM
  table1 t1
WHERE
  t1.colA BETWEEN 1 AND 2
  AND t1.pk_id IN (
    SELECT
      CASE
        WHEN SUM(t2.amt) OVER (PARTITION BY t2.colB) <> 0 THEN t2.pk_id
        ELSE NULL
      END
    FROM table1 t2
    WHERE t2.colA = t1.colA
  )

Unfortunately this surfaced the real problem. Because this is a subquery, I can only iterate through one value of the t1.colA range at a time. This appears to make it impossible execute the analytic sum within that range in the subquery. Because I can only modify the SQL within the IN clause, I don't see a solution to this problem. If anyone has any suggestions please let me know. Thanks.

I get the following error: ORA-00904: "T1"."COLA" invalid identifier. — copo24, May 24 '13 at 00:09

score 0 · Answer 1 · answered May 24 '13 at 00:39

If you know what the between values are and can use those in your subquery, then you can add that to your subquery instead:

SELECT
    *
FROM
    table1 t1
WHERE
    t1.colA BETWEEN 1 AND 2
    AND t1.pk_id IN (
        SELECT
            t2.pk_id
        FROM 
            (
                SELECT
                    t3.pk_id,
                    SUM(t3.amt) OVER (PARTITION BY t3.colB) amt
                FROM table1 t3
                WHERE t3.colA BETWEEN 1 AND 2
            ) t2
        WHERE
            t2.amt <> 0
    )

SQL Fiddle Demo

This is part of a generated SQL statement and unfortunately those values change depending on other conditions, so I can't rely on them being the same. All I'm able to due is inject my SQL into the IN clause. Thanks for the SQL Fiddle link though, I didn't know about that site - very helpful. — copo24, May 24 '13 at 00:52

score 0 · Answer 2 · answered May 24 '13 at 03:01

You can rewrite your query like this:

SELECT *
FROM table1 t1
WHERE t1.colA BETWEEN XXXX AND XXXX and
      t1.pk_id IN (
        SELECT t2.pk_id
        FROM (SELECT t3.pk_id, t3.ColA, SUM(t3.amt) as amt
              FROM table1 t3
              group by t3.pk_id, t3.ColA
              having sum(t3.amt) > 0
             ) t2
        WHERE t2.colA = t1.colA
      )

From here, you can rewrite it as:

select t1.*
from table1 t1 join
     (SELECT t3.pk_id, t3.ColA, SUM(t3.amt) as amt
      FROM table1 t3
      group by t3.pk_id, t3.ColA
      having sum(t3.amt) > 0
     ) t2
     on t1.pk_id = t2.pk_id and t1.ColA = t3.ColA
WHERE t1.colA BETWEEN XXXX AND XXXX

Unfortunately, I cannot rewrite the entire query. In this situation, I can only change the segment within the IN clause. — copo24, May 24 '13 at 03:55

Is there a workaround to the Oracle Correlated Subquery Nesting Limit?

2 Answers2