Two Methods for Generating Number Ranges in Teradata

Roland Wenzlofsky

April 28, 2023

minutes reading time


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”.

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

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>