How to create range from 1 to 100 in Firebird select statement?

Question

How to create range from 1 to 100 in Firebird select statement?

I've already found the way to convert string to list, but it doesn't work for me because of much bigger range I need to generate How to input an array parameter of values to Firebird Stored Procedure?.

Is it possible to do such things without creation of stored procedures?

Answer 1

Besides the suggestion by Gordon, you could also use a selectable stored procedure to achieve this:

create procedure generate_range(startvalue integer, endvalue integer)
    returns (outputvalue integer)
as
begin
    outputvalue = startvalue;
    suspend;

    while (outputvalue < endvalue) do
    begin
        outputvalue = outputvalue + 1;
        suspend;
    end
end

You can then use this as:

select outputvalue from generate_range(1, 100);

The benefit over the CTE as shown by Gordon is that this allows for larger ranges, as Firebird CTEs have a recursion limit of 1024.

Answer 2

One method is a recursive CTE:

with recursive n as (
      select 1 as n
      from rdb$database
      union all
      select n.n + 1
      from n
      where n < 100
     )
select n.n
from n;