SQL Query to return N rows from dual

Question

I want to write a SQL query which accepts a bind variable (say :NUM) and its output consists of one column & :NUM number of rows, each row having its row number. i.e. if we pass :NUM as 7, the output should be:

VAL
====
1
2
3
4
5
6
7

There shouldn't be any actual DB tables in query and no PL/SQL code should be used. i.e. only dual should be used in the query

Is there any way to achieve this?

Answer 1

Try something like:

SELECT 1 AS Val FROM dual
UNION ALL SELECT 2 FROM dual
UNION ALL SELECT 3 FROM dual
UNION ALL SELECT 4 FROM dual
UNION ALL SELECT 5 FROM dual
UNION ALL SELECT 6 FROM dual
UNION ALL SELECT 7 FROM dual;

It's messy, but it'll do the trick.

Edited: Ah - you need to pass in a variable to let you know how high to go...

So how about something like:

SELECT t1.Val + t2.Val * 2 + t3.Val * 4 + t4.Val * 8 AS Val
FROM
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t1, 
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t2, 
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t3, 
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t4
WHERE t1.Val + t2.Val * 2 + t3.Val * 4 + t4.Val * 8 <= 7;

Ok... editing again, now using WITH:

WiTH 
A0 AS (SELECT 0 as N FROM DUAL UNION ALL SELECT 0 FROM DUAL),
A1 AS (SELECT 0 as N FROM A0, A0 AS B),
A2 AS (SELECT 0 as N FROM A1, A1 AS B),
A3 AS (SELECT 0 as N FROM A2, A2 AS B),
A4 AS (SELECT 0 as N FROM A3, A3 AS B),
A5 AS (SELECT 0 as N FROM A4, A4 AS B),
A6 AS (SELECT 0 as N FROM A5, A5 AS B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY N) AS Val FROM A6)
SELECT *
FROM Nums
WHERE Val <= :NUM
;

Answer 2

Depends on database various method can be used.

PostgreSQL has a nice feature -- series.

To get what you want just want:

SELECT * FROM generate_series(1, NUM);

Answer 3

You could use:

 WHERE ROWNUM <= :NUM

...but the table has to contain row equal or greater to the limit in the bind variable. This link demonstrates various row number generation techniques in Oracle.

Using CONNECT BY, Oracle 10g+:

SELECT LEVEL
  FROM DUAL
CONNECT BY LEVEL <= :NUM

Confirmed by monojohnny that the bind variable can be used. Attempts to run on Oracle 9i, though CONNECT BY syntax is supported results in an ORA-01436 error.

The only thing I'm not 100% on is if the CONNECT BY will accept the limit from the bind variable.

Reference:

• Integer Series Generators - CONNECT BY LEVEL Method

Answer 4

I'm marking this community wiki since it doesn't actually answer your requirement for no tables, but one of the first things we do when installing a database is to create a set of tables for this sort of purpose.

• A table containing a large number of integers (e.g., -99999 through 99999).
• A table containing every date from 10 years in the past to 10 years in the future (which is continuously added to each month and trimmed occasionally).
• A table containing each hour of the day.

By doing this, we greatly reduce the complexity, and increase the speed, of a large number of our queries at the cost of (minimal and cheap) disk space.

You should give some serious thought to that. Aside from maintaining the date table, there's not a lot of upkeep needed.

Answer 5

I didn't come up with this answer [ so make sure any votes go the right way!!] , it just my testing notes based on 'OMG Ponies' [who wasn't sure whether the method would work with binding variable] above for reference:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> var num_rows number
SQL> begin select 20 into :num_rows from dual;
  2  end;
  3  /

PL/SQL procedure successfully completed.

SQL> select level from dual
  2  connect by level <=:num_rows;

     LEVEL
----------
         1
         2
         3
         4
 ...

Answer 6

Another solution would require some PL/SQL to create a function to return a collection with the rows... Not as simple as the select level from dual connect by level <= :b1 approach, but it's useful in a few situations:

1) Create a number table object type ( number_tbl, in this example ) :

create or replace type number_tbl as table of number;

2) Create a function that will receive the number of rows to be generated, and then return a number_tbl object with the results:

create or replace function get_rows( i_num_rows number ) return number_tbl as
  t number_tbl := number_tbl();
begin
  if i_num_rows < 1 then
    return null;
  end if;

  t.extend( i_num_rows );

  for i in 1..i_num_rows loop
    t(i) := i;
  end loop;

  return t;
end get_rows;

3) select from your function using the table( ... ) function to turn your number_tbl object into something selectable:

select * from table( cast ( get_rows( :b1 ) as number_tbl ) );