Is There a Nesting Limit for Correlated Subqueries in Some Versions of Oracle

Is there a nesting limit for correlated subqueries in some versions of Oracle?

Recent versions of Oracle do not have a limit but most older versions of Oracle have a nesting limit of 1 level deep.

This works on all versions:

SELECT  (
        SELECT  *
        FROM    dual dn
        WHERE   dn.dummy = do.dummy
        )
FROM    dual do

This query works in 12c and 18c but does not work in 10g and 11g. (However, there is at least one version of 10g that allowed this query. And there is a patch to enable this behavior in 11g.)

SELECT  (
        SELECT  *
        FROM    (
                SELECT  *
                FROM    dual dn
                WHERE   dn.dummy = do.dummy
                )
        WHERE   rownum = 1
        )
FROM    dual do

If necessary you can workaround this limitation with window functions (which you can use in SQL Server too:)

SELECT  *
FROM    (
        SELECT  m.material_id, ROW_NUMBER() OVER (PARTITION BY content_id ORDER BY resolution DESC) AS rn
        FROM    mat m
        WHERE   m.material_id IN
                (
                SELECT  con.content_id
                FROM    con_groups
                JOIN    con
                ON      con.content_id = con_groups.content_id
                WHERE   con_groups.content_group_id = 10
                )
        )
WHERE   rn = 1

Oracle nested multiple subqueries

What do you mean by "does not work"? The structure you seem to want works perfectly well in Oracle

SQL> select ename from (
  2    select ename, empno from (
  3      select ename, empno, job from (
  4        select ename, empno, job, mgr from (
  5          select * from emp
  6        )
  7      )
  8    )
  9  );

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

Correlated queries - In derived tables, can I have many nesting levels?

MySQL's limit on levels of nesting subqueries is 63, the same as the number of tables joined.

https://github.com/mysql/mysql-server/blob/8.0/sql/sql_lex.cc#L85

static constexpr const int MAX_SELECT_NESTING{sizeof(nesting_map) * 8 - 1};

nesting_map is a type defined as uint64_t a 64-bit integer. Therefore sizeof(nesting_map) is 8 bytes, and MAX_SELECT_NESTING works out to 64-1.

If you do exceed that number of levels of nesting subqueries, you get this error:

ERROR 1473 (HY000): Too high level of nesting for select

Correlated subquery with join in oracle 11.2 select clause doesn't works

Someone please correct me if I am wrong, but without ORDER BY clauses in you sub-selects, you are not guaranteed to get different members.

I believe this SQL will get you the desired results. I have tested it on Oracle 11.

WITH
    aset
    AS
        (SELECT MEMBER
              , group#
              , ROW_NUMBER ()
                    OVER (
                        PARTITION BY group# ORDER BY MEMBER
                    )
                    rn
           FROM v$logfile)
SELECT a.group#
     , a.sequence#
     , a.archived
     , a.status
     , member1
     , member2
  FROM v$log a
       LEFT OUTER JOIN (SELECT MEMBER AS member1, group#
                          FROM aset
                         WHERE rn = 1) aset1
           ON a.group# = aset1.group#
       LEFT OUTER JOIN (SELECT MEMBER AS member2, group#
                          FROM aset
                         WHERE rn = 2) aset2
           ON a.group# = aset2.group#;

Oracle - correlated subquery problems

I'm not sure why Peter is using a Min(owner) analytic function instead of first_value(owner). I believe the latter gives you what you need while the min(owner) is giving you the "minimum" owner. Everything else in the query I agree with:

Select Distinct acc_num
From (
      Select
            acc_num,
            owner,
            first_value(owner) Over ( Partition By acc_num
                  Order By a_date Desc, b_date Desc, c_date Desc
                ) recent_owner
      From ac_tab
     )
Where owner = '1234567'
      And owner = recent_owner
Order By acc_num;

Raise an error if a subquery returns no result

The way you describe it it sounds as if you need to mark a number of records based on whenever some values exist in a table. If PLSQL is an option you can just use the no_data_found exception:

invalid_data CONSTANT NUMBER := -70;
    
   DECLARE
     l_var NUMBER;
   BEGIN
     SELECT 1
       INTO l_var
       FROM dual
      WHERE 1 <> 1;
 
   EXCEPTION WHEN no_data_found THEN
     /* do your updating or error handling */  RAISE_APPLICATION_ERROR(invalid_data ,'Value is missing');
   END;

---

Related Topics