Common Table Expression in Sub-Query

Question

I would request for help in understanding which all RDBMS from Oracle, DB2, Sybase support a common table expression (CTE) in a sub-query. I am aware that PostgreSQL does while MS SQL Server does not.

SELECT a.*, b.* 
 FROM (WHERE aa as (
          <<select_query>),
          SELECT * 
            FROM aa
           WHERE <<criteria>>
    ) as a
    LEFT JOIN (
        WITH bb as (
            <<select_query>
        ),
        select * from bb inner join tbl_c on <<innerjoin>> where <<criteria>>
    ) as b
    on <<join_expr>>

I am unable to define the with clause outside the sub-queries - both the queries are dynamically generated w.r.t. the columns, criteria, security, etc. Also, the above query itself may be used in another query as a sub-query. In summary, the principle is dynamically generated views, re-usable later. Some queries may have upto 10-12 such dynamic views being merged together as well. The problem is that the application is supposed to be database-agnostic at least so far as PG, Oracle & DB2 are concerned and features not supported by one are not implemented at all.

Answer 1

Yes, you can use CTE's in subqueries in Oracle. From the Oracle 11g docs:

You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries. For recursive subquery factoring, the query name is even visible to the subquery that defines the query name itself.

As an example, this works in Oracle:

SELECT a.*, b.*
  FROM (WITH aa AS
         (
           SELECT LEVEL l1, mod(level, 5) m1 FROM dual CONNECT BY LEVEL < 50
         )
         SELECT * FROM aa WHERE m1 < 3) a LEFT JOIN
       (WITH bb AS 
         (
           SELECT LEVEL l2, mod(level, 5) m2 FROM dual CONNECT BY LEVEL < 50
         )
         SELECT * FROM bb WHERE m2 BETWEEN 1 AND 4) b
        ON a.l1 = b.l2;