title: How to query multi level bullet point x.x.x in Oracle?
body: <p>Typical employee data <a href="http://sqlfiddle.com/#!9/41d151/1/0" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/41d151/1/0</a> as example data</p>
  
  <p>I need to get result like this</p>
  
  <pre><code>    +-----+-----------+-------+
      | ID  |   NAME    | rnum  |
      +-----+-----------+-------+
      | 100 | King      |     1 |
      | 102 | De Haan   |   1.1 |
      | 103 | Hunold    | 1.1.1 |
      | 148 | Cambrault |   1.2 |
      | 170 | Fox       | 1.2.1 |
      | 169 | Bloom     | 1.2.3 |
      |          &lt;...&gt;          |
      | 147 | Errazuriz |   1.3 |
      | 166 | Ande      | 1.3.1 |
      | 167 | Banda     | 1.3.2 |
      |          &lt;...&gt;           
      | 521 | Smith     | 5.14.3|
      +-----+-----------+-------+
  </code></pre>
  
  <p>Main problem is <em>rnum</em> generation. I can't pass <em>e.level_</em> to subquery.</p>
  
  <pre><code>WITH data AS (-- more useful employee list
                SELECT id, name, level_order, level level_, manager_id, CONNECT_BY_ROOT id root_manager
                FROM employee
                CONNECT BY PRIOR employee_id = manager_id
                START WITH manager_id IS null
                ORDER BY level, level_order)
  SELECT e.id, e.name, sys_connect_by_path((SELECT e2.rnum
                                            FROM (-- employee at same level, sorted and numerated
                                                  SELECT row_number() OVER (ORDER BY t.level_order) rnum, t.employee_id
                                                  FROM data t
                                                  WHERE t.level_ = e.level_
                                                  ORDER BY t.level_order) e2
                                            WHERE e2.employee_id = e.employee_id), '.')
  FROM employee e
  CONNECT BY PRIOR e.employee_id = e.manager_id
  START WITH e.manager_id is null
  /
  </code></pre>
  
  <p>Is there a way to pass <em>level</em> to nested query?
  Or query same data in more elegant way? Looks like one of common case scenario for me.</p>