title: How to get depth of recursive SQL using WITH rather than CONNECT BY in Oracle?
body: <p>This simple example table will illustrate the recursive relationship...</p>
  
  <pre><code>CREATE TABLE tree
      (
      key NUMBER(5) NOT NULL PRIMARY KEY,
      name VARCHAR(15) NOT NULL,
      treeHier NUMBER(5)
      );
  
  INSERT INTO tree VALUES('11','Software',NULL);
  INSERT INTO tree VALUES('22','OS','11');
  INSERT INTO tree VALUES('33','Linux','22');
  INSERT INTO tree VALUES('44','Windows','22');
  INSERT INTO tree VALUES('55','DB','11');
  INSERT INTO tree VALUES('66','Oracle','55');
  INSERT INTO tree VALUES('77','MS-SQL','55');
  
  COMMIT;
  </code></pre>
  
  <p>Using Oracle 12c and a feature that has been around for many versions, I can make a recursive query to get all the child nodes using CONNECT BY and START WITH.</p>
  
  <pre><code>SELECT
      LPAD(' ',LEVEL*2,' ')||' '||
      name||' '||
      key||' '||
      NVL(TO_CHAR(treeHier),'NULL')
  FROM
      tree
  START WITH
      key = 11
  CONNECT BY
      treeHier = PRIOR key
  ORDER BY
      key ASC;
  </code></pre>
  
  <p>The pseudo column LEVEL makes it possible to know the depth of the recursion and indent the result.</p>
  
  <pre><code>   Software 11 NULL
       OS 22 11
         Linux 33 22
         Windoze 44 22
       DB 55 11
         Oracle 66 55
         MS-SQL 77 55
  
  7 rows selected.
  </code></pre>
  
  <p>Since 11g I believe, Oracle has allowed coders to use the WITH clause to create recursive queries.</p>
  
  <pre><code>WITH myRecurse(key,name,treeHier) AS
      (
          SELECT
              tree.key, tree.name, tree.treeHier
          FROM
              tree
          WHERE
              tree.key = 11
      UNION ALL
          SELECT
              tree.key, tree.name, tree.treeHier
          FROM
              tree
          JOIN
              myRecur ON tree.treeHier= myRecurse.key
          ORDER BY
              3 DESC
      )
  SELECT
      name||' '||
      key||' '||
      NVL(TO_CHAR(treeHier),'NULL')
  FROM
      myRecurse;
  </code></pre>
  
  <p>However, the LEVEL pseudo column appears to be unavailable to indent the output.</p>
  
  <pre><code>Software 11 NULL
  OS 22 11
  DB 55 11
  Linux 33 22
  Windoze 44 22
  Oracle 66 55
  MS-SQL 77 55
  
  7 rows selected.
  </code></pre>
  
  <p>Is there a methodology to use LEVEL or some alternative so I can indent using the WITH clause and get output that looks like the CONNECT BY, START WITH method?</p>