Oracle nested multiple subqueries

Question

I am looking for a reliable algorithmic way to nest queries in Oracle without having to parse and rebuild SQL statements. In DB2, MYSQL, and SQLITE I can simply nest from the largest columns set in the inner subquery to the smallest column set in the very outer query like this:

  SELECT A, B FROM (
       SELECT A, B, C FROM (
            SELECT A, B, C, D FROM (
                 SELECT * FROM myTable
                 )
            )
      )
 )

This does not work for Oracle, I had to use the WITH clause but that allows me one level of nesting like

 WITH Q1 as (
      SELECT * FROM myTable 
            ) 
            SELECT A, B FROM Q1

is there an algorithmic way to nest select statements in Oracle like DB2 or MySQL above

You can always assume it is a single table. The queries are generated automatically

Found the root cause:

My code assumes an "AS alias" is needed and appends it to the subquery as in "Select A, B from (select A, B, C from myTable) AS Q1" --- Oracle does not like that and gives an error "missing right parenthesis"

You have one-too-many right parentheses in the code you posted (four rights but only three lefts). Share and enjoy. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jul 15, 2013 at 2:03

Justin Cave · Accepted Answer · 2013-07-14 22:18:43Z

1

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.

answered Jul 14, 2013 at 22:18

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

seedhom Over a year ago

If I was wrong (and I hope I am) then my code failed on the Oracle wrapper only and I am investigating why. Thank you in advance.

Collectives™ on Stack Overflow

Oracle nested multiple subqueries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related