select statement to list numbers in range

Question

In DB2, I have this query to list numbers 1-x:

select level from SYSIBM.SYSDUMMY1 connect by level <= "some number"

But this maxes out due to SQL20450N Recursion limit exceeded within a hierarchical query.

How can I generate a list of numbers between 1 and x using a select statement when x is not known at runtime?

You should probably be using standard recursive SQL syntax rather than Oracle proprietary `CONNECT BY`, particularly when working with DB2. — mustaccio, Sep 29 '15 at 19:06

score 1 · Accepted Answer · edited May 23 '17 at 12:29

I found an answer based on this post:

WITH d AS
 (SELECT LEVEL - 1 AS dig FROM SYSIBM.SYSDUMMY1 CONNECT BY LEVEL <= 10)
SELECT t1.n
  FROM (SELECT (d7.dig * 1000000) + 
               (d6.dig * 100000) + 
               (d5.dig * 10000) + 
               (d4.dig * 1000) + 
               (d3.dig * 100) + 
               (d2.dig * 10) + 
               d1.dig AS n
          FROM d d1
    CROSS JOIN d d2
    CROSS JOIN d d3
    CROSS JOIN d d4
    CROSS JOIN d d5
    CROSS JOIN d d6
    CROSS JOIN d d7) t1
  JOIN ("subselect that returns desired value as i") t2
    ON t1.n <= t2.i
 ORDER BY t1.n

score 1 · Answer 2 · answered Feb 11 '20 at 15:46

1

That's how I usually create lists:

For your example

numberlist (num) as
(
    select min(1) from anytable 
    union all
    select num + 1 from numberlist
    where num <= x
)

answered Feb 11 '20 at 15:46

not2savvy

2,902
3
22
37

score 0 · Answer 3 · answered Sep 29 '15 at 17:36

0

I did something like this when I wanted a list of values to correspond with months:

with t1 (mon) as (                                        
  values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
)
select * from t1

It seems a bit kludgy, but for a small list like 1-12, or even 1-50, it did what I needed it to.

It's nice to see someone else tagging their questions with DB2.

answered Sep 29 '15 at 17:36

Kendall

381
1
9

Thanks for the input, but the solution I need is for a dynamic value, not known ahead of time. I'll update the question to be more clear about that. – Paul Sep 29 '15 at 17:39
@Paul You know, your query works for me, even for lists of 10,000,000 numbers, though it takes a few seconds at that level. – Kendall Sep 29 '15 at 17:44
Perhaps the recursion limit is a DB2 property that we have configured differently? – Paul Sep 29 '15 at 17:51
2

I wonder. If you're doing this kind of query a lot, have you considered creating a permanent numbers table? There seems to be a lot of advocacy out there for that approach. – Kendall Sep 29 '15 at 17:56

score 0 · Answer 4 · 2015-10-02T13:45:24.757

0

If you have any table known to have more than x rows, you can always do:

select * from (
   select row_number() over () num
      from my_big_table 
) where num <= x

or, per bhamby's suggestion:

select row_number() over () num
   from my_big_table 
   fetch first X rows only

edited Oct 02 '15 at 13:45

answered Oct 02 '15 at 12:43

For DB2, it would be much more efficient to do something like `select row_number() over () num from YOUR_BIG_TABLE fetch first X rows only`. Your method is likely going to create a work file to generate the `ROW_NUMBER`s and then filter the where. If your table is really huge, that will take a **long** time. – bhamby Oct 02 '15 at 13:15
@bhamby, just tested on a table of 1.5 billion rows in DB2 (9.7 LUW) and that wasn't the case. It was optimized appropriately. However, added your suggestion as it is a bit more elegant. – Oct 02 '15 at 13:47
I ran mine of z/OS... definitely could be a difference there. – bhamby Oct 03 '15 at 02:20

score 0 · Answer 5 · answered Mar 28 '19 at 12:46

For DB2 you can use recursive common table expressions (cf. IBM documentation on recursive CTE):

with max(num) as (
  select 1 from sysibm.sysdummy1
)
,result (num) as (
  select num from max
  union ALL
  select result.num+1
  from result
  where result.num<=100
)
select * from result;

select statement to list numbers in range

5 Answers5