title: Oracle Top 5 Problems of Top 5 Sites
body: <p>Hi all I'm new to this board, before I always viewed anonymously but now I have a specific Problem I cannot find the answer.</p>
  <p>I have two statements which do it individually very good</p>
  <p>Statement 1: Select Sites with most Problems</p>
  <pre><code>WITH ordered_query AS
  (SELECT debitorid, objectid, count(incident) as CTINCI
     FROM   ssrs_tblx_sla STSLA
     WHERE Debitorid = :Debitor 
     group by debitorid, objectid
     ORDER BY count(Incident) DESC, debitorid)
  SELECT debitorid, objectid, CTINCI
  FROM   ordered_query
  WHERE  rownum &lt;= 5
  </code></pre>
  <p>Statement 2: Most Errorcodes for a Site</p>
  <pre><code>WITH ordered_query AS
    (SELECT debitorid, objectid, errorcode, count(incident) as CTINCI
     FROM   ssrs_tblx_sla STSLA
     WHERE Debitorid = :Debitor 
     and   objectid = :Objectid 
     group by debitorid, objectid, errorcode
     ORDER BY count(Incident) DESC, debitorid)
  SELECT debitorid, objectid, errorcode, CTINCI
  FROM   ordered_query
  WHERE  rownum &lt;= 5
  </code></pre>
  <p>In both Statements I use oracle parameters in the where statement.</p>
  <p>Now I want to comibe them and tried this by a LEFT JOIN Statement, but it seems I cannot use values of Statement 1 in the where clause in Statement 2.</p>
  <p>GOAL: I want to get the Top 5 Sites with the highest amount Incidents and per site of them the Top 5 of hightst amount Incidents per errorcode</p>
  <p>Anyone an Idea what I do wrong ? I'm a little bit confused</p>
  <pre><code>select  T5S.Debitorid
  ,       T5S.Objectid
  ,       T5S.CTINCI as SiteTotal
  ,       T5P.ERRORCODE
  ,       T5P.CTINI as ERCTotal
  FROM
          (WITH ordered_query AS
              (SELECT debitorid, objectid, count(incident) as CTINCI
              FROM   ssrs_tblx_sla STSLA
              WHERE Debitorid = :Debitor 
              group by debitorid, objectid
              ORDER BY count(Incident) DESC, debitorid)
          SELECT debitorid, objectid, CTINCI
          FROM   ordered_query
          WHERE  rownum &lt;= 5
          ) T5S
  LEFT JOIN
          (WITH ordered_query AS
              (SELECT debitorid, objectid, errorcode, count(incident) as CTINCI
              FROM   ssrs_tblx_sla STSLA
              WHERE Debitorid = T5S.Debitorid 
              and   objectid = T5S.Objectid 
              group by debitorid, objectid, errorcode
              ORDER BY count(Incident) DESC, debitorid)
          SELECT debitorid, objectid, errorcode, CTINCI
          FROM   ordered_query
          WHERE  rownum &lt;= 5
          ) T5P
  ON      T5S.Debitorid = T5P.DebitorId and T5S.objectid=T5P.objectid
  </code></pre>
  <p>The error code oracle give me is:</p>
  <blockquote>
  <p>ORA-00904: &quot;T5S&quot;.&quot;OBJECTID&quot;: invalid identifier<br />
  00904. 00000 -  &quot;%s: invalid identifier&quot;<br />
  *Cause:<br />
  *Action:<br />
  Fehler in Zeile: 20 Spalte: 56</p>
  </blockquote>