Break ranges into rows Teradata

I have an input like this:

Start     End
1         100

I would link to break each range into rows:

ID
1
2
3
4
.
.
.
100

I’m trying to get this output in TERADATA, can you guys help me?

Thanks a lot.

Answer

Teradata’s proprietary EXPAND ON syntax is for creating time series, but can used for your task, too. Assuming TD16.20+ this can be further simplified using a time series function:

If start and end are positiv INTegers:

select t.*
   ,TD_TIME_BUCKET_NUMBER(time '00:00:00.000000+00:00', begin(pd), microseconds(1)) - 1 -- TD 16.20
from mytable as t
expand on -- works on date/time only -> convert int to period
   period(time '00:00:00.000000+00:00' + (interval '0.000001' second * start_)
         ,time '00:00:00.000001+00:00' + (interval '0.000001' second * end_)
         ) as pd 

For BIGINT it’s a bit more complex and must be nested:

select dt.*
   ,start_ + rownum - 1
from
 (
   select t.*
      ,TD_TIME_BUCKET_NUMBER(timestamp '0001-01-01 00:00:00.000000+00:00', begin(pd), microseconds(1) ) as rownum
   from mytable as t
   expand on -- works on date/time only -> convert int to period
      period(timestamp '0001-01-01 00:00:00.000000+00:00'
            ,timestamp '0001-01-01 00:00:00.000001+00:00' + (interval '0.000001' second * (end_ - start_))
            ) as pd 
 ) as dt
 ;
 

This allows creating up to 9,999,999,999 rows, if you need more (up to 863,999,999,999,999) you might switch to an INTERVAL DAY TO SECOND, but then you should better rethink you data model 🙂