title: Rewriting Subquery Within Select Due to Oracle Materialized View Limitation
body: <p>Oracle poses a limitation on using a subquery within the select clause when creating a materialized view. When you do so, you receive the error "ORA-22818: subquery expressions not allowed here".</p>
  
  <p>Due to this limitation, I've been struggling to rewrite the query and move the subquery out of the select clause. The query is recursively building a path using parent/child relationships, and I'm trying to also indicate if a particular category is a leaf category by joining the table back to itself and seeing if the record has a child.</p>
  
  <pre><code>SELECT A.PRODUCTCATEGORYID, A.PARENTCATEGORYID, SYS_CONNECT_BY_PATH(A.LABEL, ':') "PATH", 
  (
          SELECT CASE WHEN MAX(PRODUCTCATEGORYID) IS NOT NULL THEN 0 ELSE 1 END 
          FROM PRODUCT_CATEGORY
          WHERE parentcategoryid = A.PRODUCTCATEGORYID
  ) as "LEAF"
  FROM PRODUCT_CATEGORY A
  CONNECT BY PRIOR A.PRODUCTCATEGORYID = A.PARENTCATEGORYID
  START WITH A.PARENTCATEGORYID IS NULL;
  </code></pre>
  
  <p>Can anyone point me in the right direction of how I should go about rewriting this so the subquery is not part of the select clause?</p>
  
  <p>Thanks in advance.</p>