ORACLE SQL:Get all integers between two numbers

Question

Is there any way to select the numbers (integers) that are included between two numbers with SQL in Oracle; I don't want to create PL/SQL procedure or function.

For example I need to get the numbers between 3 and 10. The result will be the values 3,4,5,6,7,8,9,10.

Thx.

Answer 1

The first thing I do when I create a new database is to create and populate some basic tables.

One is a list of all integers between -N and N, another is a list of dates 5 years in the past through 10 years in the future and the last is a list of all hours throughout the day. For example:

create table numbers (n integer primary key);
insert into numbers values (0);
insert into numbers select n+1 from numbers; commit;
insert into numbers select n+2 from numbers; commit;
insert into numbers select n+4 from numbers; commit;
insert into numbers select n+8 from numbers; commit;
insert into numbers select n+16 from numbers; commit;
insert into numbers select n+32 from numbers; commit;
insert into numbers select n+64 from numbers; commit;
insert into numbers select n+128 from numbers; commit;
insert into numbers select n+256 from numbers; commit;
insert into numbers select n+512 from numbers; commit;
insert into numbers select n+1024 from numbers; commit;
insert into numbers select n+2048 from numbers; commit;
insert into numbers select n+4096 from numbers; commit;
insert into numbers select n+8192 from numbers; commit;
insert into numbers select -n from numbers where n > 0; commit;

This is for DB2/z which has automatic transaction start which is why it seems to have naked commits. Yes, it takes up space (although not a lot) but it makes my queries much easier to write, simply by selecting values from those tables rather than trying to figure out how to do it for each DBMS.

Your particular query would be a simple:

select n from numbers where n >=3 and n <= 10;

The hour figures and date ranges are quite useful for the sort of reporting applications we work on. It allows us to create zero entries for those hours of the day (or dates) which don't have any real data so that, instead of:

Date       | Quantity
-----------+---------
2009-01-01 |        7
2009-01-03 |       27
2009-01-04 |        6

we can instead get:

Date       | Quantity
-----------+---------
2009-01-01 |        7
2009-01-02 |        0
2009-01-03 |       27
2009-01-04 |        6

Answer 2

Or you can use Between

Select Column1 from dummy_table where Column2 Between 3 and 10

Answer 3

This trick with Oracle's DUAL table also works:

SQL> select n from
  2  ( select rownum n from dual connect by level <= 10)
  3  where n >= 3;

         N
----------
         3
         4
         5
         6
         7
         8
         9
        10

Answer 4

SQL> var N_BEGIN number
SQL> var N_END number
SQL> exec :N_BEGIN := 3; :N_END := 10

PL/SQL procedure successfully completed.

SQL>  select :N_BEGIN + level - 1 n
  2     from dual
  3  connect by level <= :N_END - :N_BEGIN + 1
  4  /

         N
----------
         3
         4
         5
         6
         7
         8
         9
        10

8 rows selected.

This uses the same trick as Tony's. Note that when you are using SQL*Plus 9, you have to make this query an inline view as Tony showed you. In SQL*Plus 10 or higher, the above is sufficient.

Regards, Rob.

Answer 5

You can use the MODEL clause for this.

SELECT c1 from dual
  MODEL DIMENSION BY (1 as rn)  MEASURES (1 as c1)
  RULES ITERATE (7)
  (c1[ITERATION_NUMBER]=ITERATION_NUMBER+7)

Answer 6

Gary, to show the result that he explained, the model query will be:

SELECT c1 FROM DUAL MODEL DIMENSION BY (1 as rn)
MEASURES (1 as c1) RULES ITERATE (8) (c1[ITERATION_NUMBER]=ITERATION_NUMBER+3) ORDER BY rn

;)

I always use:

SELECT (LEVEL - 1) + 3 as result FROM Dual CONNECT BY Level <= 8

Where 3 is the start number and 8 is the number of "iterations".