title: Common Table Expression in Sub-Query
body: <p>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.</p>
  
  <pre><code>SELECT a.*, b.* 
   FROM (WHERE aa as (
            &lt;&lt;select_query&gt;),
            SELECT * 
              FROM aa
             WHERE &lt;&lt;criteria&gt;&gt;
      ) as a
      LEFT JOIN (
          WITH bb as (
              &lt;&lt;select_query&gt;
          ),
          select * from bb inner join tbl_c on &lt;&lt;innerjoin&gt;&gt; where &lt;&lt;criteria&gt;&gt;
      ) as b
      on &lt;&lt;join_expr&gt;&gt;
  </code></pre>
  
  <p>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 &amp; DB2 are concerned and features not supported by one are not implemented at all.</p>