title: Hierarchical query in Oracle - out of control?
body: <p>I have a hierarchical query in Oracle 10 SQL that used to work. However, I removed the materialized view it was based on, and now I can't get it to come out properly, even leaving that view out altogether. </p>
  
  <p>The original query looked like this:</p>
  
  <pre><code>select oh.name, oh.description
    , sys_connect_by_path(groupname, ':') "Groups"
    , (select count(*) 
        from ml.lastobsmv 
        where lastobsmv.hdid = oh.hdid) as obscount
  from ml.obshead oh
    join ml.hiergrps hg on oh.groupid = hg.groupid
      connect by prior hg.groupid = hg.parentid
  </code></pre>
  
  <p>I presume it still works, but without the lastobsmv view, I can't test it.</p>
  
  <p>If I trim it down to </p>
  
  <pre><code>select oh.name, oh.description
  from  ml.obshead oh
    join ml.hiergrps hg on oh.groupid = hg.groupid
  </code></pre>
  
  <p>it still works, returning 41K records. However, when I use the connect by clause, it goes out of control, returning millions of records (I usually have to cancel it before getting an accurate count).</p>
  
  <pre><code>select oh.name, oh.description
    , sys_connect_by_path(groupname, ':') "Groups"
  from ml.obshead oh
    join ml.hiergrps hg on oh.groupid = hg.groupid
      connect by prior hg.groupid = hg.parentid
  </code></pre>
  
  <p>Am I missing something really blatant here, or am I misunderstanding the way this is supposed to work? Thanks.</p>
  
  <hr>
  
  <p>Vadim,</p>
  
  <p>It should return a list of Observation Terms along with the group they're in. For example,</p>
  
  <p>Obshead:</p>
  
  <pre><code># CYCLE DAYS, number of days in menstrual cycle, 100
  </code></pre>
  
  <p>HierGrps:</p>
  
  <pre><code>100, 50, Gynecology
  50, 10, Tx
  10, 0, Basic
  </code></pre>
  
  <p>should produce</p>
  
  <pre><code># CYCLE DAYS, number of days in menstrual cycle, :Basic:Tx:Gynecology
  </code></pre>
  
  <p>(eventually along with the number of times this obs term has been used, but I'll worry about that later).</p>