To generate a list of numbers or series in Oracle or SQL Server, we need to use recursive CTE, if we call the CTE within the CTE then it is called recursive CTE
SQL Server:
; WITH REC_CTE (ROW_NUM) AS (
--create first record, if we want to start the series from 10 then change it from 1 to 10
SELECT 1 AS ROW_NUM
UNION ALL
--calling REC_CTE within REC_CTE and add value 1 to get next number
SELECT ROW_NUM+1 FROM REC_CTE
--limit the number of rows
WHERE ROW_NUM<100
)
--now select the CTE to get the list of values
SELECT * FROM REC_CTE;
Oracle 11g version 2 and higher:
WITH REC_CTE (ROW_NUM) AS (
--create first record, if we want to start the series from 10 then change it from 1 to 10
SELECT 1 AS ROW_NUM FROM DUAL
UNION ALL
--calling REC_CTE within REC_CTE and add value 1 to get next number
SELECT ROW_NUM+1 FROM REC_CTE
--limit the number of rows to 100
WHERE ROW_NUM<100
)
--now select the CTE to get the list of values
SELECT * FROM REC_CTE;
Oracle:
Oracle provides a set of hierarchy functions such as START WITH, LEVEL, PRIOR, CONNECT BY, CONNECT_BY_ROOT, see this link for more info
Example1:
--below script will retrieve first hundred numbers
SELECT ROWNUM FROM DUAL
--limit the number of rows to 100
CONNECT BY ROWNUM <= 100;
Example2:
--we can use level if we want to create a series from number other than 1
SELECT LEVEL LVL FROM DUAL
-- if we want to start the series from 10 then change it from 3 to 10
WHERE LEVEL >=3
--limit the number of rows to 100
CONNECT BY LEVEL<100
If you need to check the version of oracle, use below script
SELECT * FROM V$VERSION
WHERE BANNER LIKE 'ORACLE%';
Above we have added 1 when we called the CTE with in CTE, if we add 2 then we will get even number series and so on.
Thank you for reading this article, check out my other SQL Server vs. Oracle posts.