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 only takes a minute to sign up.
Sign up to join this community
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
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;
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.
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).
WITH T3(Years) AS ( SELECT 2008 Years UNION ALL SELECT Years + 1 FROM T3 WHERE Years < 2011 ) SELECT * FROM T3;
Sep 21, 2012 at 1:25
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
Sep 21, 2012 at 15:10
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.
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
Increase only in one with the rownum select rownum + 100 from "table" order by 1;
That result with 101, 102 etc.
