title: LISTAGG equivalent for 10g
body: <p>I'm on Oracle 10g and so unfortunately I can't use <code>listagg</code> (as this requires 11g).</p>
  
  <p>So I would like to port the code below to an alternative for 10g:</p>
  
  <pre><code>select count(*) from (
      select listagg(flag) within group (order by dt) as flags
      from   temp_output_ovlp
  )
  where not regexp_like(flags, 'HH|EE|HS|SE');
  </code></pre>
  
  <p>This will search for matching <code>HH|EE|HS|SE</code> pattern within <code>EHEEESHEHH</code> when run against:</p>
  
  <pre><code>|----------|----------|
  |  DT      |  FLAG    |
  |----------|----------|
  |  10      |  E       |
  |  20      |  H       |
  |  30      |  E       |
  |  40      |  E       |
  |  50      |  E       |
  |  60      |  S       |
  |  70      |  H       |
  |  75      |  E       |
  |  80      |  H       |
  |  100     |  H       |
  |----------|----------|
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!4/1415f/2" rel="nofollow noreferrer">demo</a></p>
  
  <p>I tried <code>lag</code> as suggested <a href="https://stackoverflow.com/questions/50433925/how-to-store-sql-result-in-a-variable-and-parse-the-result-to-identify-a-possibl">here</a>, available on 10g, but without success:</p>
  
  <pre><code>select (case when count(*) = sum(case when flag2 not in ('HH', 'EE', 'HS', 'SE')
               then 1 else 0
          end) as return_value
  from (select t.*,
               (lag(flag) over (order by dt) || flag) as flag2
        from temp
       ) t;
  </code></pre>
  
  <p><strong>Questions</strong></p>
  
  <ol>
  <li>Can someone tell me how I can make it work?</li>
  <li>How does <code>COLLECT</code> (see <a href="http://www.oracle-developer.net/display.php?id=306" rel="nofollow noreferrer">here</a>) compare to <code>lag</code> in term of performance? </li>
  </ol>
  
  <p>Thanks</p>
  
  <p>EDIT:</p>
  
  <p><strong>Expected results</strong></p>
  
  <ul>
  <li><code>EHEEESHEHH</code> must return <code>1</code> as at least one of the following two letters patern (<code>HH|EE|HS|SE</code>) can be found (here <code>EE</code> and <code>HH</code>) in <code>EHEEESHEHH</code>.</li>
  <li>If the pattern were let say <code>EHESSSHEHE</code> then <code>0</code> must returned as none of the following two letters pattern (<code>HH|EE|HS|SE</code>) can be found in <code>EHESSSHEHE</code>.</li>
  </ul>
  
  <p><strong>Note</strong>: The pattern (eg <code>EHEEESHEHH</code>) if the result of the inner query <code>SELECT listagg(FLAG) WITHIN GROUP (ORDER BY DT) as flags FROM TEMP_OUTPUT_OVLP</code> against the data table.</p>