title: Oracle - Any way to write this query in a single Select statement?
body: <p>I have the following query:</p>
  
  <pre><code>SELECT * FROM (
      SELECT * FROM View_ExecutiveForecastReport
      WHERE WORKGROUPID IN (94)
  )
  PIVOT  (SUM(COURSELENGTH) AS LENGTH FOR (WORKGROUPID) IN (
  94 as "B2B",
  66 as "CFS",
  69 as "800IB",
  76 as "TSData",
  99 as "RetailSales",
  103 as "Telesales"
  ))
  </code></pre>
  
  <p>I'd like to instead write it using a single SELECT instead of nesting.  I've tried:</p>
  
  <pre><code>SELECT * FROM View_ExecutiveForecastReport
  WHERE WORKGROUPID IN (94)
  PIVOT  (SUM(COURSELENGTH) AS LENGTH FOR (WORKGROUPID) IN (
  94 as "B2B",
  66 as "CFS",
  69 as "800IB",
  76 as "TSData",
  99 as "RetailSales",
  103 as "Telesales"
  ))
  </code></pre>
  
  <p>But I get the error: <em>ORA-00933: SQL command not properly ended</em></p>
  
  <p>I've also tried:</p>
  
  <pre><code>SELECT * FROM View_ExecutiveForecastReport
  PIVOT  (SUM(COURSELENGTH) AS LENGTH FOR (WORKGROUPID) IN (
  94 as "B2B",
  66 as "CFS",
  69 as "800IB",
  76 as "TSData",
  99 as "RetailSales",
  103 as "Telesales"
  ))
  WHERE WORKGROUPID IN (94)
  </code></pre>
  
  <p>Which gives me the error: <em>ORA-00904: "WORKGROUPID": invalid identifier</em></p>
  
  <p>If I remove the WHERE clause, the query works fine.  I'm also open to solutions that use a CTE.</p>