Two Methods for Generating Number Ranges in Teradata

Method 1, avoiding any helper tables or functions which are only available in new releases of Teradata:

WITH RECURSIVE NumberRanges(TheNumber,TheString) AS
(
SELECT 1 AS TheNumber,casT(1 as VARCHAR(500)) as TheString
		
FROM
( 
  SELECT * FROM (SELECT NULL AS X) X
) DUMMYTABLE
UNION ALL
SELECT
	TheNumber + 1 AS TheNumber,
	TheString ||',' || TRIM(TheNumber+1) 
	
FROM NumberRanges
WHERE
TheNumber < 10
)
SELECT TheString
FROM NumberRanges
QUALIFY ROW_NUMBER() OVER ( ORDER BY TheNumber DESC) = 1

Method 2, with helper tables and XMLAGG:

SELECT 
     TRIM(TRAILING ',' FROM (XMLAGG(TRIM(rn)|| ',' ) (VARCHAR(255))))
 FROM 
     (SELECT 1 + ROW_NUMBER() OVER (ORDER BY Sys_Calendar."CALENDAR".day_of_calendar) as rn 
 FROM Sys_Calendar."CALENDAR" QUALIFY rn <= 10) t;

Both methods deliver the string "1,2,3,4,5,6,7,8,9,10".

Want more practical data engineering analysis like this?

Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Launch access is open. The single paid plan will be EUR 49 per year.

Get the next issue

Which process do you believe is using fewer Disk IOs and CPU resources?

Planning or surviving an enterprise data platform migration?

I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.

Subscribe before the paid plan launches and keep launch access.

Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.