If you’re using the GENERATE_SERIES() function to create a series of numbers, but you’re finding that only the first value in the series is returned, it could be something very obvious.

The obvious reason this could happen is that your step value is too big. In particular, if the step is so big that it covers the whole series, then it stands to reason that there will only be one value in the series.

If this is an issue for you, you might want to check that you’re using an appropriate step value. Using a smaller step value can help to create a series with more values.

Example

Here’s an example of this issue:

SELECT value FROM GENERATE_SERIES( 1, 5, 5 );

Result

value      
-----------
          1

Here, I tried to create a series that increments from 1 to 5. The only problem is that my step value is 5 too. This results in only one value being returned in my series. That’s because the next value in the series (based on my step value) would be greater than the end point of the series.

Solution 1 – Reduce the Step Size

One obvious solution is to reduce the step size to a more realistic amount:

SELECT value FROM GENERATE_SERIES( 1, 5, 1 );

Result

value      
-----------
          1
          2
          3
          4
          5

In this case I reduced the step value to 1, which resulted in each row in the series being incremented by 1. Therefore we ended up with 5 rows in our series.

Solution 2 – Adjust the Start/End Points

It’s also possible that you’ve used the wrong start or end point for your series. This could inadvertently result in less values being returned than you expected.

In my example above, I tried to create a series from 1 to 5, but maybe I meant to create one from 1 to 50 (and accidentally omitted the zero). Or maybe I meant to have an end point of 25 and omitted the leading 2 from 25. You get the picture.

Either way, this is easily fixed:

SELECT value FROM GENERATE_SERIES( 1, 25, 5 );

Result

value      
-----------
          1
          6
         11
         16
         21

So if you’re finding that your series is returning only one row (or less rows than expected), check that the arguments are the correct ones for your series.