9

I'm trying to search the DB2 equivalent of generate_series() (the PostgreSQL-way of generating rows). I obviously don't want to hard-code the rows with a VALUES statement.

select * from generate_series(2,4);

    generate_series
    -----------------
                    2
                    3
                    4
    (3 rows)
maja
  • 17,250
  • 17
  • 82
  • 125
Steve Schnepp
  • 4,620
  • 5
  • 39
  • 54

2 Answers2

9

The where clause needs to be a bit more explicit about the bounds of the recursion in order for DB2 to suppress the warning. Here's a slightly adjusted version that does not trigger the warning:

with dummy(id) as (
    select 2 from SYSIBM.SYSDUMMY1    
    union all
    select id + 1 from dummy where id < 4
)
select id from dummy
3

I managed to write a recursive query that fits :

with dummy(id) as (
    select 2 from SYSIBM.SYSDUMMY1    
    union all
    select id + 1 from dummy where id + 1 between 2 and 4
)
select id from dummy

The query can be adapted to whatever for(;;) you can dream of.

Steve Schnepp
  • 4,620
  • 5
  • 39
  • 54
  • Result is ok, but it generates a warning on db2 v9 SQL0347W The recursive common table expression "LOGINNAME.DUMMY" may contain an infinite loop. SQLSTATE=01605 – Boune May 05 '09 at 19:37
  • Yes, I also have it in DB2v8. I don't know how to remove it though. – Steve Schnepp May 06 '09 at 06:53