title: Can one not reference a CTE twice in a query in Oracle?
body: <p>I have a chain of CTEs, and at the end I want to select from the last one twice. Oracle lets me do either of the selects but not a union of both of them (even if I do "select * from (select union select)". The only thing I could narrow down the problem to was referring to "runinfo" in two selects, but that in and of itself is not the problem as Shannon shows.</p>
  
  <pre><code>WITH lastTen AS (
  SELECT id
  FROM (SELECT autobuild_id,  id, rank() OVER (PARTITION BY autobuild_id ORDER BY id DESC) as rank
        FROM runs
        WHERE status='FINISHED' AND type='FULL' AND build_failed in ('n', 'N'))
  WHERE rank &lt;= 10
  ),
  
  recentAvg AS (
  SELECT autobuild_id, avg(elapsed) avgtime
  FROM runs
  JOIN lastTen ON (runs.id = lastTen.id)
  GROUP BY autobuild_id
  ),
  
  runinfo AS (
  SELECT autobuildid, runid, changelist, status, age
  FROM (
      SELECT runs.autobuild_id autobuildid, runs.id runid, changelist, runs.status status, runs.create_date, a.avgtime,
         CASE WHEN status = 'RUNNING' THEN TO_NUMBER(sysdate - start_date)*86400 -- in seconds to compare to elapsed
              WHEN status = 'TO BE' THEN TO_NUMBER(sysdate - create_date) -- in days
         END AS age
      FROM runs
      LEFT JOIN recentAvg a ON (runs.autobuild_id = a.autobuild_id)
      )
  WHERE (status = 'RUNNING' AND age &gt; avgtime * 1.5)
     OR (status = 'TO BE' AND age &gt; 1)
  ORDER BY autobuildid, runid DESC
  ),
  
  running AS (
  SELECT autobuilds.id, name, runid, changelist, runinfo.status, age
  FROM runinfo
  JOIN autobuilds ON (runinfo.autobuildId=autobuilds.id)
  WHERE runinfo.status='RUNNING'
  ),
  
  tobe AS (
  SELECT autobuildid, name, runid, changelist, status, age
  FROM (SELECT autobuildid, name, runid, changelist, runinfo.status, age, RANK() OVER (PARTITION BY autobuildid ORDER BY runid DESC) AS rank
       FROM runinfo
       JOIN autobuilds ON (runinfo.autobuildid=autobuilds.id)
       WHERE runinfo.status='TO BE')
  WHERE rank=1
  )
  
  SELECT * FROM running
  UNION ALL
  SELECT * FROM tobe
  </code></pre>