title: Build numbers table on the fly in Oracle
body: <p>How do I return a rowset consisting of the last four years based on the current date?</p>
  
  <p>If this query runs on 12/31/2010 it should return:</p>
  
  <pre><code>2007
  2008
  2009
  2010
  </code></pre>
  
  <p>But if it is run on 1/1/2011 it should return:</p>
  
  <pre><code>2008
  2009
  2010
  2011
  </code></pre>
  
  <p>Here's what I started with, two queries that return the starting year. I prefer the second as converting to string feels a bit dirty to me.</p>
  
  <pre><code>SELECT TO_CHAR(TRUNC(sysdate, 'YY') - INTERVAL '3' YEAR, 'YYYY') FROM DUAL;
  SELECT EXTRACT (YEAR FROM sysdate) - 3 FROM DUAL;
  </code></pre>
  
  <p>But I don't know how to generate rows to flesh this out. In SQL Server I'd use a CTE as in <a href="http://sqlservercode.blogspot.com/2007/04/interview-with-itzik-ben-gan-author-of.html" rel="nofollow">the fn_nums function on this page</a>.</p>