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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 10 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
3  
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

Here is an example of adding multiple flag and incrementing that based on case statement.

WITH T3(FLAG1,FLAG2,FLAG3,tt,OTHER_DATA)  
AS (    
SELECT '0' FLAG1, '0' FLAG2, '0' FLAG3 , current_timestamp  tt , 'dummy'  OTHER_DATA 
FROM dual 
UNION ALL  
SELECT case when cast( FLAG2 as int) > 5 then
cast ((cast(FLAG1 as int) + 1) as varchar2(30)) else  FLAG1 end FLAG1,
cast((cast(FLAG2 as int) + 1) as varchar2(30)) FLAG2  ,case when (
(FLAG2 ='3') or (FLAG2 = '4')) then cast ((cast(  FLAG3 as int) + 1)
as varchar2(30)) else FLAG3 end FLAG3  ,current_timestamp  tt ,
'ACTUAL' OTHER_DATA FROM T3 WHERE FLAG2 < 10   
)
SELECT * FROM T3
WHERE OTHER_DATA != 'dummy' ;

-- Result set is below

Flag1   Flag2   Flag3   TT                                              OTHER_DATA
0       1       0       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
0       2       0       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
0       3       0       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
0       4       1       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
0       5       2       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
0       6       2       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
1       7       2       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
2       8       2       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
3       9       2       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
4      10       2       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL   
share|improve this answer
2  
Why all the casting between strings and numbers? Not entirely sure what this adds to the existing answers as it isn't something the OP seemed to be looking for. – Alex Poole Dec 21 '15 at 15:53

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.