SQL: counter

counter.sql

-- Returns a list numbers:
--   from and including FROM value 
--   until and including TO value
--   and optional STEP can be either positive or negative. Default to +1 for step up and -1 for step down
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
--
-- Simply paste this gist into ACS SQL and step through the example.
--
-- It is a cool example how far you can go with SQL: Have fun 😀  
-- (C) Niels Liisberg 2022
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either expressed or implied.
----------------------------------------------------------------------------------------------
create or replace function QUSRSYS.counter  (
    from bigint, 
    to   bigint,
    step bigint default null
) 
returns table ( counter bigint  )
set option output=*print, commit=*none, datfmt=*iso, dbgview = *source --list
begin

    declare i bigint;

    if step is null then 
        set step = case when to < from 
            then -1
            else 1
        end;  
    end if;

    case 
        when step = 0 then return;
        when step < 0 and to > from then return;
        when step > 0 and to < from then return;
        else begin end;
    end case; 


    set i = from;
    
    case 
        when step > 0 then 
            while i <= to do
                pipe (i);
                set i = i  + step;
            end while; 
        when step < 0 then 
            while i >= to do 
                pipe (i);
                set i = i  + step;
            end while; 
    end case;    
    return;
end;

-- Test case
select * from table(QUSRSYS.counter (from => 1, to => 5             )); -- default to step 1
select * from table(QUSRSYS.counter (from => 1, to => 5 , step => 1 ));
select * from table(QUSRSYS.counter (from => 1, to => 5 , step => 2 ));
select * from table(QUSRSYS.counter (from => 1, to => 5 , step => -1)); -- Invalid step, No data - OK 

select * from table(QUSRSYS.counter (from => 5, to => 1              )); -- default to step -1
select * from table(QUSRSYS.counter (from => 5, to => 1 , step => -1 )); 
select * from table(QUSRSYS.counter (from => 5, to => 1 , step => -2 )); 
select * from table(QUSRSYS.counter (from => 5, to => 1 , step =>  1 )); -- Invalid step, No data - OK