Build numbers table on the fly in Oracle
How do I return a rowset consisting of the last four years based on the current date?
If this query runs on 12/31/2010 it should return:
2007 2008 2009 2010
But if it is run on 1/1/2011 it should return:
2008 2009 2010 2011
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.
SELECT TO_CHAR(TRUNC(sysdate, 'YY') - INTERVAL '3' YEAR, 'YYYY') FROM DUAL; SELECT EXTRACT (YEAR FROM sysdate) - 3 FROM DUAL;
But I don't know how to generate rows to flesh this out. In SQL Server I'd use a CTE as in the fn_nums function on this page.
Here is one way:
SELECT yr FROM ( SELECT EXTRACT (YEAR FROM (ADD_MONTHS ( SYSDATE, - ( (LEVEL - 1) * 12)))) yr FROM DUAL CONNECT BY LEVEL <= 4) ORDER BY yr;
SELECT yr FROM ( SELECT EXTRACT (YEAR FROM sysdate) - (level -1 ) yr FROM DUAL CONNECT BY LEVEL <= 4) ORDER BY yr;
SELECT yr FROM (SELECT EXTRACT (YEAR FROM SYSDATE) - (x - 1) yr FROM DUAL MODEL DIMENSION BY (1 AS z) MEASURES (1 x) RULES ITERATE (4) (x [ITERATION_NUMBER] = ITERATION_NUMBER + 1)) ORDER BY yr;
To display numbers from 1 to 100:
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100 ORDER BY LEVEL
To change the max, change it in the third line.
Similar to the accepted answer you can replace the inline view with a with clause. I find the with clause more readable. In particular if you are going to perform multiple calculations against the on-the-fly NUMBERS table as part of the same query - the entire SQL statement is more readable.
Numbers table on the fly:
WITH NUMBERS_START_AT_ZERO AS (SELECT LEVEL - 1 AS NUM FROM DUAL CONNECT BY LEVEL <= 4 order by NUM desc) SELECT EXTRACT (YEAR FROM sysdate) - NUM AS YEARS FROM NUMBERS_START_AT_ZERO
YEARS 2009 2010 2011 2012