Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

how to create incremental number in oracle sql query without create any table ? I have tried using "with" clause, but I failed to get the expected result. I am using oracle 10g

here is the code that I try,it seems not working:

WITH
TABLE3 AS ( SELECT 2008 YEARS FROM dual WHERE 1=1
union all
select t3.YEARS+1 from TABLE3 t3
WHERE 1=1 AND t3.YEARS < 2011
)

select YEARS from TABLE3

expected result I want is :

2008
2009
2010
2011
share|improve this question

4 Answers 4

up vote 7 down vote accepted

Similar to Kerri's answer, but without the with (and inspired by an SO answer):

SELECT 2007 + LEVEL AS YEARS
FROM DUAL
CONNECT BY LEVEL <= 4;

     YEARS
----------
      2008
      2009
      2010
      2011

Or if your aim is to get the current year the three preceding it, without hard-coding the start year:

SELECT EXTRACT(YEAR FROM SYSDATE) + 1 - LEVEL AS YEARS
FROM DUAL
CONNECT BY LEVEL <= 4
ORDER BY YEARS;
share|improve this answer
1  
i think i will pick this one for my code, its much more simple than using With clause –  50LV3R Jun 4 '11 at 2:59

I think this will work (based on this page ( http://psoug.org/definition/LEVEL.htm ) as a starting point):

WITH counter
AS ( SELECT LEVEL seq
       FROM DUAL
     CONNECT BY LEVEL <= 4 )
SELECT (2008 + seq - 1) myYear
  FROM counter
 ORDER BY 1
;

This should return:

myYear
------
  2008
  2009
  2010
  2011

Adjust 2008 and 4 to get different results.

share|improve this answer

Why not just create a sequence?

CREATE SEQUENCE TEMP_YEAR_sEQUENCE START WITH 2008;

SELECT TEMP_YEAR_sEQUENCE.NEXTVAL FROM DUAL; 

....

DROP SEQUENCE TEMP_YEAR_SEQUENCE;

EDIT:

For small ranges of sequence values you can use something like this:

select ROWNUM + 10   # start value
from ALL_OBJECTS 
where ROWNUM <= 5 ;  # count of values 

You just need a table with a sufficient number of rows.

share|improve this answer
2  
Seems like a lot of overhead for something so trivial, and the DDL will do an implicit commit that might not be expected. And the user issuing the query might not have permission to create a sequence. –  Alex Poole Jun 3 '11 at 11:55
    
i agree with Alex Poole, but still, it is another workaround thanks anyway –  50LV3R Jun 4 '11 at 3:01
    
-1 for the reasons @AlexPoole stated. if you reexecute the query without recreating the sequence you get a different result. –  miracle173 Sep 21 '12 at 4:54
    
the query that uses the sequence does not return the desired set of numbers. –  miracle173 Sep 21 '12 at 5:31

It looks like the OP was attempting to solve the problem using a recursive subquery. This won't work in 10g because that functionality wasn't added until 11.2, but in 11.2+ the following would also be a valid solution to the problem.

WITH T3(Years) AS (
   SELECT 2008 Years FROM dual
   UNION ALL
   SELECT Years + 1 FROM T3 WHERE Years < 2011
   )
SELECT * FROM T3;

The only thing missing from the OP's query was (YEARS).

share|improve this answer
    
slightly modified works in MS SQL as well WITH T3(Years) AS ( SELECT 2008 Years UNION ALL SELECT Years + 1 FROM T3 WHERE Years < 2011 ) SELECT * FROM T3; –  miracle173 Sep 21 '12 at 1:25
    
@miracle173 Interesting, just remove the FROM dual. –  Leigh Riffel Sep 21 '12 at 12:02
    
dualis an oracle specific table. Other databases like MS SQL Sever, mysql, postgres allow statements like select expression. mysql does know the a dual table too –  miracle173 Sep 21 '12 at 15:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.