title: pivot in oracle gives error for sql query after in
body: <p>I have a table commission_new as output of commission_new table:</p>
  
  <p><a href="https://i.stack.imgur.com/wQ5xQ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wQ5xQ.jpg" alt="enter image description here"></a></p>
  
  <p>when I use the SQL:</p>
  
  <pre><code> select * from  
      (   select employee_no,years ,subtotal from
          commission_new
      )
      pivot
      (  sum(subtotal) for years in (1999,2001,2000,2003)
          --(select distinct nvl(years,0) from commission_new)   
      )
  order by employee_no;
  </code></pre>
  
  <p>I get the result</p>
  
  <p><a href="https://i.stack.imgur.com/VtCZm.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VtCZm.jpg" alt="enter image description here"></a></p>
  
  <p>but if I use</p>
  
  <pre><code>(select distinct nvl(years,0) from commission_test )
  </code></pre>
  
  <p>instead of </p>
  
  <pre><code>(1999,2001,2000,2003)
  </code></pre>
  
  <p>I get the error:</p>
  
  <p><a href="https://i.stack.imgur.com/a1EeZ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/a1EeZ.jpg" alt="enter image description here"></a></p>
  
  <blockquote>
    <p>Error at Line: 7 Column: 6 ORA-00936: missing expression</p>
  </blockquote>
  
  <p>even though the SQL <code>select distinct nvl(years,0) from commission_test</code> gives the same years as result.
  What may be the reason of this?</p>