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

Question

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?

Mark Rotteveel · Answer 1 · 2017-03-13T09:11:01.950

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.

score 6 · Accepted Answer · edited Mar 12 '17 at 23:06

6

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;

edited Mar 12 '17 at 23:06

Thijs van Dien

6,516
1
29
48

answered Mar 12 '17 at 17:41

Gordon Linoff

1,242,037
58
646
786

1

Note it works as of Firebird 2.1, but is limited to 1024 elements; see https://firebirdsql.org/refdocs/langrefupd21-select.html#langrefupd21-select-cte If you go beyond that you get this error: `Too many concurrent executions of the same request`. – Jeroen Wiert Pluimers Apr 10 '17 at 19:29

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

2 Answers2